| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2011-01-21 : 13:20:56
|
GreetingsTrying to wrap my head with optimization, fun funI have a clustered unique index with two int type fieldsbooks table book_id int PK ISBN_id int FKindex is book_id ASC, ISBN_id ASC There are over 17million+ rowsIt is doing clustered index seek with Cost: 48%, the query is fast but I want to plan for scaling when there could be n-million rows. Is there any way to plan for that so that things do not slow down as more rows are added other than partitioning? Can I reduce that 48% cost?Thanks!If you don't have the passion to help people, you have no passion |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-21 : 13:56:14
|
| The cost has to go somewhere. The cost of all operators has to sum to 100%. Also note those costs are estimates.Can you post the query, table and index definitions and execution plan please.--Gail ShawSQL Server MVP |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-01-21 : 14:13:22
|
ha! SET STATISTICS IO ONSET STATISTICS TIME ONexposes what is going on under the hood! thanks!I see who is the culprit! I think. tbl3 in red has some issues need to check indexesTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0Table 'tbl1'. Scan count 0, logical reads 181380, physical reads 0Table 'tbl2'. Scan count 1, logical reads 3000, physical reads 0Table 'tbl3'. Scan count 29917, logical reads 129528, physical reads 00.Table 'tbl4'. Scan count 65, logical reads 12919, physical reads 0Table 'tbl5'. Scan count 1, logical reads 3, physical reads 0Table 'tbl6'. Scan count 0, logical reads 3, physical reads 0Table 'tbl7'. Scan count 1, logical reads 9466, physical reads 0reads 0.Table 'tbl8'. Scan count 1, logical reads 210, physical reads 0 If you don't have the passion to help people, you have no passion |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-21 : 16:02:35
|
| Ignore the scan count. It DOES NOT mean that the table was scanned. Focus on the logical reads--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-01-21 : 16:36:10
|
| Thank you allworking on DMV report and checking out what SQL recommends via execution plan. What should I specifically be looking out for in the ep?If you don't have the passion to help people, you have no passion |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-22 : 03:25:49
|
| It may suggest a missing index. If you're using SQL 2008's management studio it will be obvious. Test it, don't assume SQL's right.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-22 : 11:03:16
|
| 129528 scan indicates you are retrieving a hell lot of rows from the table.What is the average row size of the table?PBUH |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-02-02 : 11:44:05
|
| does the fact that my high logical read tables are in another database than my main app database make a difference?my query returns 666176 rows in 1 minute and 26 seconds in SSMS. On the UI end it times out. I have added the recommended indexes and still real slow. I am dealing with 25million+ rows on the core tableIf you don't have the passion to help people, you have no passion |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-02-02 : 13:17:59
|
| >> I have a clustered unique index with two INTEGER type fields [sic] <<This looks really awful to me -- used to own some bookstores. Let's fix that skeleton table. Columns are not fields, let's get the terms right. There is also no such thing as an "isbn_code" -- it is just the "isbn" and it is not an integer. And since it is an industry standard, it ought to the the PRIMARY KEY. What the heck is a "book_id" anyway? CREATE TABLE Books (isbn CHAR(13) NOT NULL PRIMARY KEY CHECK (isbn LIKE '978[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), book_id INTEGER NOT NULL, etc);You say you have:CREATE INDEX foobar ON Books (book_id ASC, isbn ASC);Knowing the industry, I would drop the "book_id" and correct the ISBN's data type. Then scrub the data to be sure the check digit is right (which is probably is not with INTEGER data). Then you can use substrings to get to languages and publishers encoded in the ISBN. That means the PRIMARY KEY has already given you an index. I hope this was not your real problem. Can you post the actual problem? --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
optimization question