Adding columns from existing table increases time?

[Borvik]

I'm working on a search query, and I like the results it pulls but I dislike how much time it takes to run - so I've been attempting to narrow down where the slowdown is.

If you guys really need to see the query, I can post it - but I'd like to try doing it without it first.

The part of the query that actually does the searching (a subquery table) returns 1167 rows in less than a second (returning a productid and a RANK).

I've joined this result with our product table adding the productid field from that table (an integer) to the results - this also adds filtering of hidden products and categories. The 192 rows are returned in about 1 second.

So I start slowly adding fields (from tables already in the query so no new joins or anything).

mfgpartno - no speed change
productname - it now takes 1:20 for those 192 rows

productname is a varchar(500) field.

Any ideas why just adding a column from a table already in the query would add so much time to it?

Thanks.

 

[gmmastros]

I have an idea.

You probably already know what an index is, but you may not be familiar with the concept of a covering index. This is more of a concept than it is a "SQL Thing". An index is considered a covering index when ALL of the columns in a query are included in the index. This can greatly increase the speed of a query because when ALL of the columns are in an index, SQL can perform the query from the index (without actually looking at the table). Since the table data is usually much larger than index data, this speeds up your operation.

When you added the Product Name column, I'm guessing that SQL Server probably could no longer use a covering index and is forced to go back to the table to get the data. If I am right, looking at the execution plan will probably confirm this. I'm guessing that without the Product Name column, you are probably getting index seek, and with the column, you probably have a clustered index scan.

Now, don't misunderstand me. I'm not suggesting that you add a varchar(500) to an index because it is likely to slow down other queries that are using that index.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Borvik]

Thanks for the response - it is quite interesting.

However here is an interesting oddity I found while attempting to increase performance.

I didn't have any indexes on the productlist table (so obviously no primary key). I thought adding one might help (there are 1.5 million records in the table), so I added on on the productid field. That slowed down the with just the productid/rank so I took the key off again and it speed up.

I started over eliminating a join for costs (which was in the above tested query) and I was able to add all the fields without an performance issues. So it was leading me to think that it is the join with the costs table - however that isn't even the case.

It appears to be coming from the filter on the list specifically:

(pl.cost IS NOT NULL OR bc.cost IS NOT NULL)

The price is then calculated using COALESCE(bc.cost, pl.cost).

It is conceivable that a product could exist in the list without a cost on it (productlist AS pl, bestcost AS bc) and the bestcost table would have a cost, but making sure one or the other is not null always ensure a cost.

Though I now do NEED to add the cost - so it can be sortable, I just need to figure out how to do this filter properly.

 

[Borvik]

For some reason I decided to check whether all the tables where indexed or not.

None of them were. (these tables are being imported into every day, by importing into a temp table deleting the old and renaming for maximum uptime)

I updated my import scripts to recreate the indexes, and it now runs much faster.

Thanks George for the tidbit on how SQL may have been behaving - I'm not sure how to tell what was happening based on what you mentioned. Sometimes just talking it out on tek-tips helps get my thinking on track - regardless of what is posted.

Cheers!