Post reply

Slow running query

  • March 28, 2005 at 11:47 am

    #109283

    Hi,

    I don't know if this is the right place to put this question, but I wasn't sure where else to put it.

    I have a table with the following definitions

    CREATE TABLE [dbo].[Table1] (

    [A] [varchar] (5),

    [varchar] (6),

    [C] [varchar] (200),

    [D] [varchar] (10) ,

    [E] [varchar] (12),

    [F] [datetime],

    [G] [char] (1) ,

    [H] [char] (1),

    [varchar] (400),

    [J] [varchar] (400),

    [K] [varchar] (400),

    [L] [varchar] (400),

    [M] [varchar] (400) ,

    [N] [char] (2) ,

    [O] [char] (2),

    [P] [char] (2),

    [char] (2)

    ) ON [PRIMARY]

    Fields A and B are the primary key. This table is the many to many relationship table to both A and B. There is a clustered unique index on A and B. There is also an index on B and A as well as an additional index just on B. There is about 100K records in this table.

    My question is whenever I try and link to this table I always take a performace hit. It always takes longer than other joins to larger tables. What would cause this to happen? 100K is not that big of a table. Is it because of the varchar fields in the keys? I haven't had any problem with that before, but I am at a loss why this would cause a slowdown.

    Thanks,

    Richard Binnington

  • March 28, 2005 at 12:40 pm

    #549178

    By the looks of things you have 3 indexes on the A & B columns (a primary key, clustered unique key and non-clustered), plus an index on B.

    I would drop the unique and nonclustered indexes on A&B, just have a clustered primary key index.


    Shamless self promotion - read my blog http://sirsql.net

  • March 28, 2005 at 12:57 pm

    #549183

    Difficult to answer without seeing the offending query(s).

    What does QA tell you when you analyse the query ? Is it table scanning this table ? are statistics up to date ?

  • March 28, 2005 at 1:19 pm

    #549186

    Thanks for the replies,

    QA says that the most of the time is spend on doing a Clustered Index Seek on the A & B Primary Key field.

    I know that we have reindexed the table if that is what you mean by are the statistics up to date.

    Edit: I have updated the statistics to no avail.

    I am writing a stored procedure that takes a string in and created a 2 column temp table that is a listing of the choices the user has made on a web page. I outer join that table to Table1 in order to limit the returns that I am saving to another temp table. All of this is try and isolate the problem to this table. The query that does this is as follows:

    select

    #tmp.A,

    #tmp.B,

    Table1.I,

    Table1.J,

    Table1.K,

    Table1.L,

    Table1.M,

    Table1.N

    into #tmp2

    from

    #tmp left outer join Table1

    on #tmp.A = Table1.A and #tmp.B = Table1.B

    Thanks,

    Richard

  • March 28, 2005 at 3:45 pm

    #549214

    Look at dbcc showcontig, run it against the table and see if you have fragmentation. If so run a reindex.

  • March 28, 2005 at 4:44 pm

    #549220

    Is this on a busy server, with other processes using tempdb at the same time as this query ? Consider creating the #temp table, then inserting into it, instead of creating the table via a "Select Into".

     

  • March 29, 2005 at 7:12 am

    #549324

    Thanks again for your replies. I have run the DBCC SHOWCONTIG and it is at 100%. I am not on a busy server. Basically I am on it by myself. There are a couple of other people but they are only doing data entry or selecting data.

    I will try changing the select...into into an insert into an existing temp table.

    Thanks,

    Richard

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply