February 19, 2008 at 2:10 am
Hi Guys,
I have the following query:
select count (distinct(isnull([columnname1],[columnname2])))
from with (nolock)
where [Date] Between '2006-02-01'
and '2007-04-01'
and [columnname3] like '%rings%'
and [columnname4] != 'rings'
There is about 98 millions rows on the table. It takes about 3 min to bring back the result set. I tried rebuilding the indexes on another server but still get back the same results. Will a query like this uaually take this long or should the query be altered in any way.
Please help.
Imke
February 19, 2008 at 5:48 am
Maybe, depends on what the table looks like. What's your intention with that complex expression in the count?
Can you post the table's schema and the index definitions please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2008 at 7:08 am
With a table that large, I doubt statistics were calculated using 100% coverage, assuming not all the search arguments in your query are the first columns in indexes.
For a start, I would view the execution plan, and see if there are large variances between actual rows and estimated rows, especially for operations that were costly. These would indicate inaccurate statistics. Try creating/rebuilding statistics for those columns, and see if the execution plan improves.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 19, 2008 at 7:09 am
Your big problem is that you are using LIKE in such a way that it's not possible to use any kind of indexing. The "Like '%ring%' " (find 'ring' anywhere in the column) is going to force a table scan. On 98 million rows - that is going to take a while.
That being said - try posting the code anyway - let's see what's "under the hood".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 19, 2008 at 7:30 am
The presence of the search argument "LIKE '%ring%'" does not automatically force a table scan. There are 2 other search arguments, both of which could cause the query optimizer to first perform an index seek if the selectivity is high enough (and if the statistics are accurate). Or if columnname3 is covered in an index, the query optimizer could still perform an index scan, which could be magnitudes faster than a table scan, depending on its size.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply