SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Should I break table having more cols into two?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajemessage
Starting Member

17 Posts

Posted - 12/05/2013 :  05:18:53  Show Profile  Reply with Quote
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.

Edited by - rajemessage on 12/06/2013 03:09:13

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 12/06/2013 :  00:28:14  Show Profile  Reply with Quote
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 - 12/06/2013 :  02:51:03  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 12/06/2013 :  03:22:31  Show Profile  Reply with Quote
"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 - 12/10/2013 :  01:43:49  Show Profile  Reply with Quote
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

Canada
594 Posts

Posted - 12/17/2013 :  10:09:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/17/2013 :  11:33:37  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000