Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Slow SQL query in Microsoft SQL Server (no joins)

Posted on 2016-10-13
Medium Priority
Last Modified: 2016-10-14

I have two databases on Microsoft SQL Server 2012, both have about 5 million rows. There are not at all the same schema, but they are on the same database on the same server. This query:

select sum(RecordCount)
 from TableAinDatabase1

Returns 5,717,342 records and takes 5 seconds

But this query:

select sum(RecordCount)
 from TableBinDatabase2

Returns 5,160,207 records and takes 1.5 minutes.

I am not a DBA, but since there are no joins (and thus I don't think indexes are an issue), I am lost on how something could be THAT much faster/slower, and I'm not sure where to look next.

Question by:eviglotti
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 79

Expert Comment

ID: 41843015
Spec on system
Is TableBinDatabase1 being queried accessed regularly, while the second is just there?
When a db is active, it is kept in memory, when you query the second, the system might have to shift the first out and then query the second.
Run the same query on the second sequentially, and then query the first again. What are the results.

Use the explain/query plan for each and see what that tells you.

Expert Comment

by:Daniel Jones
ID: 41843074
The problem troubleshooting starts with analyzing the slow query. If the difference between the estimated and actual number of rows in a query execution plan is higher than 10%, the statistics are obsolete. How the performance will be affected depends on the query and execution plan. The same obsolete statistics can have different effect on two different queries.
to know more reasons of slow running query, visit: http://www.sqlmvp.org/are-you-experiencing-slow-running-queries-in-sql-server/
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41843123
I believe the statistics on table2 are out of date. Can you update statistics and then try?

Also, I agree with the other experts. If an object is frequently accessed, SQL Server will still have it lying around in the buffer pools and hence return you the results much faster. When capturing performance statistics, it is always preferred to have a cold buffer, clean cache condition. One way to achieve this would be to run the following before you run the query:

(DISCLAIMER: Please run these statements on your Dev/QA/Staging environments only - do not use them in production.)


Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41843189
Returns 5,717,342 records and takes 5 seconds
 Returns 5,160,207 records and takes 1.5 minutes.
By the queries each one can only return a row. Is the 5M the value returned by the single row?
Are both really tables or by chance any of them is a view?
If both are tables,  do they have a clustered index?
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41843246
Can you please post your queries.

Also there is high possibility that indexes needs rebuilding and stats needs update. If these things are not updates regularly then SQL engine will give you sub optimal plan.

Try this..

ALTER INDEX ALL ON [yourtablename] REBUILD ;
UPDATE STATISTICS [yourtablename];

Note - First try using Rebuilding indexes only. If that also not worked then update the statistics.
LVL 69

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 41843916
If no index contains RecordCount, SQL must scan the entire table to do the SUM().

If TableB is being modified by another process,  your count might have to wait to read all the rows.  Also, if TableB's rows are a lot wider (more bytes per row) than TableA, then it will take longer to scan TableB.

Creating a nonclustered index containing RecordCount should significantly speed up the query, because SQL will only have to scan that index rather than the entire table.

It's also possible that TableB is fragmented and needs rebuilt or reorganized.  If you want to follow up on that, let me know, and I'll post more details on how to do that.

select sum(RecordCount)
 from TableAinDatabase1

Returns 5,717,342 records and takes 5 seconds

But this query:

select sum(RecordCount)
 from TableBinDatabase2

Author Comment

ID: 41844183
Thanks everyone on these items. A few things:

1. It is true that TableAinDatabase1 has been accessed more over the years than TableBinDatbase2, but neither are used regularly. They are both star schema reporting tables and thus are used only a couple times a day. But TableAinDatabase1 has been around for about 10+ years whereas the slower one was just created a few months ago.  I have been going back and forth on the sequence of which query I run first and second and I still see the same results.

2. Unfortunately I'm not sure that updated statistics is going to help us as I have a maintenance plan that updates statistics on both tables in these two databases, so they both have that in common.

3. Both are actual tables, not views and both have 5mil rows, not just a value of 5mil in the sum. The RecordCount field is basically a 1 for all rows, though sometimes it is a 0 depending on the type of record. But ostensibly we are looking at 5mil rows in both tables. They both have a clustered index for their single primary key.

4. As for indexes, interestingly, the indexes on the fast query have been there forever, though I rebuild them once a month or so. The indexes on the slow query table are dropped and recreated daily when we drop indexes, load data, and create indexes.

5. Neither table has an index that contains RecordCount. No process is modifying the tables during the time of the query.

However, there is no question that the size of the table makes a huge difference. I thought they had similar number of columns but I was wrong, the slow one has twice as many. I'm not sure why twice as many columns means an exponential slowdown as it just doesn't seem proportional. Maybe there is a cutoff where past too many columns it has to separate in pages or something.

We will play with the table size and go from there.


Author Closing Comment

ID: 41844185
This definitely is it. I mislead the group in that I thought the tables were about as wide, but clearly not. Thank you, we will investigate accordingly.
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41844631
Access to the wider table will be slower. SQL Server stores data in pages. Wider the row, more are the pages required to store a fixed number of rows. Speed of data access is directly proportional to the number of pages and the density of rows/page.

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question