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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Should I break table having more cols into two?

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 8060



Please 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 them

To 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?
Go to Top of Page

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 functional
behaviour of mssql 2005 in above stated situatation.

yours sincerly
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-06 : 03:22:31
"when i keep more number of cols my queriey gets slow "

SELECT Col1, Col2

has less data to transfer from database than

SELECT Col1, Col2, ... Col8060

so 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 )
Go to Top of Page

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 table
is getting slow when compaird to selecting 20 cols from a table having 20 col only int it.

yours sincerly.
Go to Top of Page

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 table
is 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?
Go to Top of Page

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 table
is 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 Optimizer
TG
Go to Top of Page
   

- Advertisement -