Slow running query

Question

Slow running query

Richard Binnington

SSChasing Mays

Points: 656
More actions
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

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

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

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 ?

Richard Binnington SSChasing Mays Points: 656 More actions · Answer 3

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 4

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

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".

Richard Binnington SSChasing Mays Points: 656 More actions · Answer 6

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