March 14, 2008 at 12:46 am
Hi,
I have 1,65,00,000 rows [16 million rows] in one table. I am using this table for SQL reports. When I use this table joining with some other tables, my report comes in 30 mins.
I tried to split table into more tables that time it is consuming more time.
Can Anyone suggest to speed up my query faster?
Thanks,
Peer Md.
March 14, 2008 at 7:33 am
What do your execution plans look like. What kind of indexing is on the table.
While 16 million rows is not small, it's not so outrageously large that you can't index it and query against it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2008 at 7:38 am
peer_mohamed2k (3/14/2008)
Hi,I have 1,65,00,000 rows [16 million rows] in one table. I am using this table for SQL reports. When I use this table joining with some other tables, my report comes in 30 mins.
I tried to split table into more tables that time it is consuming more time.
Can Anyone suggest to speed up my query faster?
Thanks,
Peer Md.
Hi,
As Grant has already hightlighted, 16M rows is a lot, but with correct indexes for your SARG's (WHERE clause) contained within report code, you should be results back quickly. So, have a look at your report code and ensure the correct indexes exist. Secondly, you may want to look at partitioning your data, as this will benefit you in terms of query performance, as optimizer will only access partitions that have the data your report requires. What sort of growth are you experiencing on this table?
Thanks,
Phillip Cox
MCITP - DBAdmin
March 14, 2008 at 12:49 pm
If you can provide a copy of the create statements for the tables involved, and the code for the query, we can probably help you narrow down the exact problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 15, 2008 at 8:40 am
To help narrow down which indexes you need to add, here is a query you can use (adjust the "migs_adv.index_advantage > 10000" filter according to your needs):
--Get missing indexes
SELECT
index_advantage
,user_seeks
,last_user_seek
,avg_total_user_cost
,avg_user_impact
, equality_columns
,included_columns
,[statement]
FROM
(SELECT
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage
,migs.* FROM sys.dm_db_missing_index_group_stats migs
) AS migs_adv
inner join
sys.dm_db_missing_index_groups AS mig
ON
migs_adv.group_handle = mig.index_group_handle
inner join
sys.dm_db_missing_index_details AS mid
ON
mig.index_handle = mid.index_handle
WHERE
migs_adv.index_advantage > 10000
ORDER BY
migs_adv.index_advantage desc
Make sure your SQL instance has been up for at least several days for sufficient stats.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 15, 2008 at 8:41 am
Also, examine your execution plan. A hash-join operator, if present, is an indication you are missing indexes on join columns.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 16, 2008 at 6:13 am
Also, the one thing I should have suggested to start with: use the Database Tuning Advisor, DTA, to get index recommendations on your slow query.
Save your query in a file.
Open SSMS, Tools --> Database Engine Tuning Advisor
Follow the instructions in the GUI.
If you need help using the DTA, let me know.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 16, 2008 at 9:46 pm
peer_mohamed2k (3/14/2008)
Hi,I have 1,65,00,000 rows [16 million rows] in one table. I am using this table for SQL reports. When I use this table joining with some other tables, my report comes in 30 mins.
I tried to split table into more tables that time it is consuming more time.
Can Anyone suggest to speed up my query faster?
Thanks,
Peer Md.
Sounds like I'm being a smarty pants, and I'm not... the real answer is check the execution plan and maybe add some correct indexes, but I'm thinking that you simply need to write a better query... or spend $100,000 US for your own private robust reporting server 😉 But no one can tell because you haven't posted the query you're having problems with. Please post it and please review the URL in my signature line.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2008 at 12:37 am
Hello Marios,
The script that you provided here, what exactly should i make up of it.?
We are just starting sqlserver development so i do not yet have a lote of users but if i change the clause to
migs_adv.index_advantage > 100
i still dont have any record affected
below 50 i get 1
below 5 i got 7 records affected
So now i was wondering is this good news or just verry bad news...
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply