Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajemessage
Starting Member
17 Posts |
 Posted - 2013-12-05 : 05:18:53
|
| To speed up my reports i am keeping reports specific data to reduce joins and calulations, they are 50000 to 200000 rows in them.but noticed that when i keep more number of cols my queriey gets slow when compaired to less no of cols.Q1) Is it correct ?Then i checked 8060 and found that data in cols are less than 8060Please tel me how should i see 8060, while making tables.should i keep less no of cols and break my table.so that only composite key will be repeated in two tables.Q2) Not only this, i also noticed that queries which are fast they become slow after clearing buffer , so i am trying themTo be fast even if they are not buffered ( so please tell me is this consideration is good).can do some thing that every thing get buffered (because it is only reporting database)yours sincerely. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-12-06 : 00:28:14
|
| That small number of rows should not cause problems. Your comments about columns are cause for concern. Can you post examples of your tables, the data in them and some of the queries you use? |
 |
|
|
rajemessage
Starting Member
17 Posts |
Posted - 2013-12-06 : 02:51:03
|
quote:
Originally posted by LoztInSpace
That small number of rows should not cause problems. Your comments about columns are cause for concern. Can you post examples of your tables, the data in them and some of the queries you use?
I won't be able to post data, i just wanted to know functionalbehaviour of mssql 2005 in above stated situatation.yours sincerly |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-06 : 03:22:31
|
"when i keep more number of cols my queriey gets slow "SELECT Col1, Col2has less data to transfer from database thanSELECT Col1, Col2, ... Col8060so it will be faster. It may also be much if Col1 and Col2 are covered by an index.Also, if Col3 ... Col8060 include varchar() or varchar(MAX) columns then the data in them may be large/huge and that will take more time to retrieve too.Also, time to retrieve the data is not the only issue, the data also has to be sent, down the wire, to the computer that requested it. More data = More time.Further consideration is that the extra, unused, columns in the SELECT may take up buffer/cache space and thus some other data is removed from the cache. So future queries will not be able to be answered from the cache ... so those queries will become slower too.You should only ever include a Column in the SELECT statement if it is actually required by the Application. For example NEVER use SELECT * (even if the you want all columns then in the future some massive varchar(MAX) may be added to the table, and that will slow down the application - and require that every place where SELECT * was used on that table needs to be changed, thus bad habit and Don't do it  ) |
|
|
|
rajemessage
Starting Member
17 Posts |
Posted - 2013-12-10 : 01:43:49
|
| I could not ask properly,i am selecting 20 cols from 150 cols of a tableis getting slow when compaird to selecting 20 cols from a table having 20 col only int it.yours sincerly. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-12-17 : 10:09:58
|
quote:
Originally posted by rajemessage
I could not ask properly,i am selecting 20 cols from 150 cols of a tableis getting slow when compaird to selecting 20 cols from a table having 20 col only int it.yours sincerly.
Try running it directly on the Server.If the 20 cols from the 150 have more information than the 20 cols from the 20, maybe you are dealing with the time to transfer the data of the network? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-17 : 11:33:37
|
quote:
Originally posted by rajemessage
I could not ask properly,i am selecting 20 cols from 150 cols of a tableis getting slow when compaird to selecting 20 cols from a table having 20 col only int it.yours sincerly.
This makes sense. each physical block of memory will represent fewer rows with a wider table. So selecting from a table with 150 columns will require many more READs then from a table with 20 columns. That is the reason best practices are for longer narrower tables (ie more rows less columns).Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Should I break table having more cols into two?