Execution plan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Execution plan

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-24 : 09:42:12

Created non clustered index on this column (key) of 2 table a1, a2.. made a join with index column only .. and select non index column date in select list .. but execution plan shows table scan ....

same query i choose index column in select list only no other changes in join .. however it shows index scan .. how it is possible

---->>>>>TABLE SCAN

SELECT STG2.DT , STG1.DT
FROM A1 STG1 (NOLOCK)
JOIN A2 STG2 (NOLOCK)
ON STG1.KEY = STG2.KEY

|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([STG1].[KEY])=([STG2].[KEY]), RESIDUAL:([STG2].[KEY]=[STG1].[KEY]))

|--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG1].[KEY]))

| |--Table Scan(OBJECT:([QIW].[dbo].[A1] AS [STG1]))

|--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG2].[KEY]))

|--Table Scan(OBJECT:([QIW].[dbo].[A2] AS [STG2]))

---->>>>> INDEX SCAN

SELECT STG1.KEY , STG2.KEY
FROM A1 STG1 (NOLOCK)
JOIN A2 STG2 (NOLOCK)
ON STG1.KEY = STG2.KEY

|--Parallelism(Gather Streams)

|--Hash Match(Inner Join, HASH:([STG2].[KEY])=([STG1].[KEY]), RESIDUAL:([STG2].[KEY]=[STG1].[KEY]))

|--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG2].[KEY]))

| |--Index Scan(OBJECT:([QIW].[dbo].[A2].[NC_INDEX1_A2] AS [STG2]))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG1].[KEY]))
|--Index Scan(OBJECT:([QIW].[dbo].[A1].[NC_Index1_A1] AS [STG1]))

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-24 : 10:21:48

In your 2nd query the all the information sql needs to return results is contained in the index. That is not the case with the 1st query. Since there is no WHERE clause i suppose the scan is necessary, in the second query I guess it's faster to scan the index rather than the table.

Be One with the Optimizer
TG

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-24 : 10:38:28

ss second qry is faster becos it shows index seek and scan .. how it shows that is my question .. where condition are same .. only select list are different ...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-24 : 10:53:26

Your second query doesn't show index seek, it shows index scan. And as I said before, the plan can certainly be affected by the SELECT list.

quote:
In your 2nd query the all the information sql needs to return results is contained in the index. That is not the case with the 1st query

Be One with the Optimizer
TG

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-24 : 10:57:58

ok it is index scan ... then there is meaning less to create index and saying create index and use it in where clause .. will improve performance.. i have seen this all documents.. then it is lie ???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-24 : 11:15:01

>>then it is lie ???
Of course not. Let's say you have a user table with 1 million Users (indexed by userid)

This query will make good use of that index:
select userid from user where userid = 45672

But this query doesn't need an index because we're returning everything from the table anyway:
selecct userid from user

having an index does not guarantee improved performance - it depends on a lot of factors.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-24 : 11:18:41

try putting this in your query and check the execution plan (assuming you still have your index on [key])

WHERE STG1.KEY = 1

Be One with the Optimizer
TG

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-24 : 11:20:12

this issue makes head break .. if i change select list column which has index then it shows index scan ...
what other factors i can see in this small qry .. just one column i joined ..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-24 : 11:30:59

But a bandage on that broken head and welcome to the wonderful world of query optimization :)

>>what other factors i can see in this small qry

well, even without a WHERE clause, if STG2 has only 1 row in it and STG1 has 100mil rows then indexes will make a HUGE difference because sql server keeps track of statistics.

But generally speaking when there is no WHERE clause and you are returning the entire table it will make little performance difference if the plan uses an index scan or a table scan. It is still a scan of all rows.

Be One with the Optimizer
TG

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-24 : 11:37:50

good i can answer ...

table can have 1034 columns ( hope i am right ) ... but i have 2030 columns in upstream ( data file ) via BCP i stored here 2 tables .. so i splited two tables -- actualy it shud be one table .. but column exceeds .. so definetely number of rows will be equal in both tables .. there will be always where clause which also has index column .. any other factor ??

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-24 : 23:36:27

>>any other factor ??
A table with more than a thousand columns is a table with way too many columns. How many rows are in these two tables? How many columns are being returned in any given query? Do you have a Clustered index on these tables? If so, what is the key?

It will be hard (if not impossible) to get good performance from tables with so many columns. Actually, I'm not sure of that statement because I don't have experience with that. But DB experts recommend narrow tables with lots of rows rather than the other way around.

Maybe we should stop talking in abstracts...What specific problem is prompting these questions? What are you trying to do?

Be One with the Optimizer
TG

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-27 : 00:05:25

>>A table with more than a thousand columns is a table with way too >>many columns. How many rows are in these two tables?
4.5 lak record are in both tables .. count will be same in both table ...

>>How many columns are being returned in any given query? Do you >>have a Clustered index on these tables? If so, what is the key?

most of the places only 2 columns .. only one place 350 column in select list .

>>Maybe we should stop talking in abstracts...What specific problem >>is prompting these questions? What are you trying to do?

index is the only way .. it shud go to table scan .. if i use index column in where clause ... there is no concurrency , 1 TB free space is here , this is only job running in DB ....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-27 : 09:46:35

I suspect that part of the slowness is due to the fact of the extremely large number of columns. You probably have only 1 row per 8k page so the data is stored over hundreds of thousands of pages. The solution there would be to normalize your table. Do you have lots of columns like type1, type2, type3, etc...?

Given that most of your queries only select 2 columns of data, spreading the data over multiple tables so that any given SELECT statement would only read from a dozens of pages rather than hundreds of thousands.

The other thing that may be a problem is where are returning this data? Just to a query window or to an application frontend or browser? Make sure to run this simply in a query window to confirm the slowness is a database issue.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources