what am I missing? - 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 2005 Forums

Transact-SQL (2005)

what am I missing?

Author

Topic

medtech26
Posting Yak Master

169 Posts

Posted - 2009-03-18 : 18:03:03

I have a table with over 4m records, id combined from to numeric columns (low_id, hi_id).

When running a query to bring one of the id's:


SELECT low_id 
FROM myTable 
WHERE low_id<=1234567 AND hi_id>=1234567;

it brings the result in less then one sec., however, when asking for some extra data:


SELECT low_id, field_1, field_2, field_3
FROM myTable 
WHERE low_id<=1234567 AND hi_id>=1234567;

it takes more then 15 sec. to find the record. Any ideas what can cause this?

BTW, when running two queries from the application, one to bring low_id value and second to bring field_1, field_2, field_3 where low_id=low_id I get results in less then 1 sec..

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-19 : 11:41:31

you can INCLUDE those additional columns in one of the indexes for low_id or high_id.. in 2000 SQL would do a bookmark lookup to get the data for these additional columns in the SELECT that are not in the index. In 2005, we have this new option to INCLUDE those columns in the index itself so the optimizer doesnt have to make an extra hop to the data page to get the data.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

medtech26
Posting Yak Master

169 Posts

Posted - 2009-03-19 : 13:32:54

I suspected it have something to do with the indexe/key, however, when trying to add some extra columns to the current index:

low_id    ASC  (numeric(18, 0))
hi_id     DESC (numeric(18, 0))

Desired index:

low_id    ASC  (numeric(18, 0))
hi_id     DESC (numeric(18, 0))
field_1   ASC  (nvarchar(2))
field_2   ASC  (nvarchar(150))
field_3   ASC  (nvarchar(150))
field_4   ASC  (nvarchar(150))

SQL throws me an error: "Total size of an index or primary Key cannot exceed 900 bytes".

BTW, I'm using SQL Server 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:52:18

then add them as included columns rather than inside index

http://www.sqlteam.com/article/included-columns-sql-server-2005

medtech26
Posting Yak Master

169 Posts

Posted - 2009-03-19 : 15:53:58

Thanks for the link, it solved the problem :-)!

For future ref., here's the relevant code to create these kind of indexes:

CREATE NONCLUSTERED INDEX [IX_myTable_1] ON [dbo].[myTable](
    [low_id] ASC, [hi_id] DESC
)INCLUDE ([field_1],[field_2],[field_3],[field_4])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 09:57:59

cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources