Which is faster

Posted Date: 31 Aug 2007 Posted By: Shraddha Member Level: Silver Points: 2 Responses: 2

I have one table which have following structure and data.

Invoice No Tax Id Tax Amount
1 1 1000
1 2 2000
1 3 3000
...
1 30 5000

2 2 5000
2 3 1500
...
2 30 9000

Because of thousand of record in table I want to access it fast and thinking of converting this table in following structure. Tax ID Is Fixed 1 - 30.

Invoice No Tax Id1 Amount1 Tax id 2 Amount 2 Tax Id 3 Amount 3 ... Tax id 30 Tax Amt 30
1 1 1000 2 2000 3 3000 30 5000
2 2 5000 3 5000 30 9000

Total Tax Id is more than 30 so column of proposed table have 30 * 2 = 60 (One for tax id and second for taxamount) but No of records is goging to be reduced bcoz for accessing invoice tax detail I have to search only one record and its 60 column. Record Size of first table is very small bcoz only 3 columns are there and record size of second table is very large bcoz of 60+ column is there.

My Question is Which structure is fast for accessing tons of data.

pls guide me. My e-mail id is dolishah@rediffmail.com

Responses

Author: Shivshanker Cheral 01 Sep 2007

Member Level: Diamond

Rating:

Points: 2

i have one idea think on this. as you told that tasix are 30 and fixed so now you table needs 30 * 2 = 60 columns but if the tasic are fixed then no need of storing them create columns like this
InvoiceNO TaxID1 TaxID2 TaxID3 ... TaxID30
and store in TasID1 column the amount means columns itself is TaxID1 so in that column store 1000 and in TasID2 store 2000 and so on then your table hase 30 + 1 = 31 columns only. we can access based on Invoice id and TaxId column name.

Author: Shivshanker Cheral 01 Sep 2007

Member Level: Diamond

Rating:

Points: 2

reply if you have any doubt

Post Reply

This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.

	Next : syntax error converting varchar value '/' to a column of datatype int
	Previous : Data and log files expand by what increment?
	Return to Discussion Forum
	Post New Message
	Category: SQL Server

Related Messages

dotNet Slackers

About Us Contact Us Privacy Policy Terms Of Use