March 26, 2013 at 7:48 am
I suspect the answer is going to be "No" but thought I'd check with more experienced heads than my own.
I have several third party applications that I'm supporting and cannot change the T-SQL they are using but I CAN optimize the hell out of their database if necessary. My question is this:
There are several tables that are being hit with "Select * from..." and those queries return incredibly slowly. There are no indexes or primary keys on these tables, but there are a few candidates should that route be helpful. So the question is.. would an index or primary key speed up a select * statement? If so, why?
March 26, 2013 at 8:14 am
Maybe.
Depends whether you make the index covering (ie duplicate the table) which you probably don't want to do, depends what % of the rows of the table are returned by the queries. If they return a small portion (< 1%), then even a non-covering index may help
You should have a primary key and a clustered index at least. They may or may not be the same thing.
Maybe take a read through these
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
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
March 26, 2013 at 8:15 am
Oh sorry, Gail, I forgot to mention that there are no where conditions.. this is pulling the entire table back.
March 26, 2013 at 8:18 am
What about order by clauses? Are there clustered indexes on these tables?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2013 at 8:24 am
Not a thing, Sean. No primary key, no indexes, nothing. And the entire select statement consists of
select * from table
Oh how I wish I could just hack in and intercept these queries.. lol
And thank you, Gail, I'm reading through those articles now.
March 26, 2013 at 8:32 am
I think, in essence, I'm always going to get a table scan with select * so indexes may not help. Let me go a different direction with this then.. what WOULD speed up a table scan?
Row level compression? Page level compression? Breaking the fingers of whoever wrote the select *? I know that would make ME feel better.. lol
March 26, 2013 at 8:54 am
Defragmentation and high fill factor (default 100), so there will be less pages to read. Optimize disk read speed (put their filegroups on RAID5) and network speed (check that networking cards are not set to AUTO, but to their declared maximum speed). Compression will help only if data is not in the cache (is rarely used) and you have extra CPU horsepower to spend.
March 26, 2013 at 8:59 am
Compression might help, though it might make writes slightly slower. Adding clustered indexes will ultimately help to ease database maintenance and allow defragmentation, but I don't think I'd expect great performance gains. Other than that, I think it may rely more heavily on having a lot of memory for caching, fast network throughput and fast disk.
March 26, 2013 at 9:08 am
Erin Ramsay (3/26/2013)
Oh sorry, Gail, I forgot to mention that there are no where conditions.. this is pulling the entire table back.
Ow, ow, ow!
Primary key and clustered index them. Primary key for data integrity, clustered index to organise the table (there are several downsides to heaps). Not much else you can really do.
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
March 26, 2013 at 9:11 am
Erin Ramsay (3/26/2013)
Row level compression? Page level compression? Breaking the fingers of whoever wrote the select *? I know that would make ME feel better.. lol
Row compression should help without too much overhead, it'll reduce the memory required and reduce the IO load. Other than that, fastest IO subsystem you can manage (RAID 10 by preference), as much memory as you can and then some. Not really much else.
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
March 26, 2013 at 9:14 am
Thanks for the advice. 🙂
I'll implement the changes and see if I can get a marginal boost in speed as well as at least providing data integrity.
March 26, 2013 at 5:03 pm
Erin Ramsay (3/26/2013)
Oh sorry, Gail, I forgot to mention that there are no where conditions.. this is pulling the entire table back.
Oh...
Oh-oh-oh.
They've got you.
No way out. Final destination. 🙂
Everything they suggested might help a little, but it won't bring you any performance improvevent beyond couple of percent.
And I bet you would not be posting here if 2-3% improvement would be good enough.
No DBMS tricks will give you anything here.
You might get noticable improvements from using SSD, running appllication of the same serverr as DB (to eliminate networking bottleneck), install as much memory as the server could handle (move as much data into memory as possible), move reporting/integration stuff to separate machine to cache the data over there, etc.
But as soon as all these measures give you desired improvements - walk away.
No, RUN!
Grab your credits and RUN!
Do not stay for a celebration - it's not gonna last long.
_____________
Code for TallyGenerator
March 27, 2013 at 7:03 am
Throw money, uh, I mean, hardware at the problem. That's your only option.
"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 27, 2013 at 8:32 am
The clustered index thing could actually make a HUGE difference, despite the no where clauses. HEAPS can wind up with a bajillion forwarded records which will crush the server during retrieval (well - usually. I honestly don't know how they would affect it with pure table scans all the time). Definitely fragmented partially filled pages could be at play.
I have come across more than a few databases with just PKs on them and no nonclustered indexes (Access upgrades mostly), but very rarely have I seen an entire database with zero indexes! You have my sympathy!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 27, 2013 at 9:01 am
Not sure if I should chime in or not but speaking as Erin's somewhat insane fearless leader 😎 ... I'm sorry to say there really isn't much we can do about this particular system. It's important but not 100% production, if that makes any sense. Plus the end users haven't complained about slowness. If they did, it'd be a good opportunity to smack tell them the evils of "select *". So it's more of a pain in the nuisance at this point since we receive alerts about slow running queries. Regardless, it's good information overall. Thanks everyone. 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply