Why is this SELECT statement so slow.

Question

Post reply

Why is this SELECT statement so slow.

adams.squared

SSCrazy

Points: 2675
More actions
December 21, 2011 at 1:21 pm

#250252

I have a script as simple as
Select a.Col1, a.Col2, a.Col3, a.Col4, a.Col5, a.Col6, b.Col4, b.Col5, b.Col6, b.Col7
From Table123 a INNER JOIN Table 456 b on
a.Col4=b.Col4
and a.Col5=b.Col5
and a.Col6=b.Col6
That runs fine and dandy. But when I change what is in line 1, with FEWER fields, it takes forever to run.
Select b.Col7, a.Col4, a.Col5, a.Col6
From Table123 a INNER JOIN Table 456 b on
a.Col4=b.Col4
and a.Col5=b.Col5
and a.Col6=b.Col6
Everything from the FROM on down is exactly the same. Why would what's in the SELECT line effect the query performance?
On my system the first script runs in 1-2 seconds. The second runs around 530 seconds.

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

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

What do you see in the execution plan?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Lowell SSC Guru Points: 323518 More actions · Answer 2

yep pseudocode will not help with pinpointing a specific performance issue; you 've got to show the real SQL statement, and preferably, the actual execution plan.

examples of why the SQL myght be slow that i can think of:

1. one of those columns returned int eh slow query is TEXT,varchar(max),varbinary, or some other huge column.

2. one query develops a plan with good, up to date statsitics based ont eh selected columns, and the other has out of date statsitics, resulting in a poor execution plan.

3. One query has indexes that aids the execution plan, and the other does not.

show us the slow execution plan and we can tell you exactly the reason it's slow and what to do about it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

Most probably the covering index is forcing Optimizer to perform better for more columns.

INCLUDE (column [ ,... n ] )

Specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Although chances are less but there might be any query running on same tables which implies that this problem doesn't come up with every run. its just a blind shot :-D;-)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

Does it still affect performance if you move the b.Col7 to *after* the selection list for a, as it is in the first example?