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 77

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 12

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 47

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 12

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
Linked Server Issue with SQL2012 3 26
T-SQL: New to using transactions 9 31
Proper Case SQL Command 2 11
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

803 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