Slow SQL query in Microsoft SQL Server (no joins)

Posted on 2016-10-13
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
LVL 78

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:
LVL 13

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 49

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 28

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 500 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 13

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Conditions in Where clause 9 46
Find unused columns in a table 12 68
Open A Form without Loading All the Records of Its Recordsource 6 44
Regarding Disk IO 3 42
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

740 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