Performance Issue - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Performance Issue

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-16 : 09:17:49

I have a dynamically generated table having 925 columns. WHen all of its columns are filled means No NULL values. Then it takes around 1 minute to retrieve data. And when I join this table with other tables the retrieval goes very slow. Please tell me how to resolve this issue?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 09:24:59

don't use 925 columns...

hate to be sarcastic but really!

break it up into several tables... surely you don't need all 925 at one time

Corey

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-16 : 09:28:57

No mention of indexes. I'm guessing, due to the fact the table is dynamically built, that there aren't proper indexes. Indexes on criteria fields (used in WHERE clause) and join fields (in JOIN expressions in FROM clause) would help your performance.

Finding a way to not dynamically build this table would also help.

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-16 : 09:44:04

Actually I have to i need scientific data due which contains too many columns. The number of columns could be 50,000. That's why I made each table consisting of 925 columns keeping in mind the row size of 8kb. Now if I further split the table then I will have too many tables. PLease give me a solution with my current scenario.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 09:50:31

Tell us a little more about the type of data you are trying to track... then we might have a solution.

indexing (like drymchaser said) would be a starting point, but my real suggestion is step back and reevaluate your design.

Corey

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-16 : 10:01:55

basically the testing of Semi conductors is done against a test plan and each test plan consists of number of tests. WHich after execution store data in dynamic tables.

table structure of a dynamic table like
mobile_05_001
wafer_sequence,
unit_number,
die_sequence,
sub_die,
x,
y,
T1,
T2,
T3,
.....
T900

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-16 : 10:04:17

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 10:10:25

whate are the T1, T2...T900 columns?? different tests?

If so, then try a table like:

mobile_05_001
wafer_sequence,
unit_number,
die_sequence,
sub_die,
HBin_NUmber,
SBin_NUmber,
Head_Number,
Site_NUmber,
Tests_Executed,
Test_Time,
x,
y,
Test_Number,
Test_Result

You may need some of the other columns either... (i.e. number of test executed could be retrieved from a count of the entries)

Corey

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-16 : 10:18:38

We have its parent table Test_Param_Map Which contains a field
column_name and it will contain T1

while mobile_05_001 will contain values of T1 in column T1 of mobile_05_001.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 10:25:49

Um... yeah.

My best suggestion to you would be redesign your table layouts.

For a given test there may be multiple params, multiple results so:

tests
tId,
tName,
tDesc

params
pId,
pName,
pDesc

test_params
tId, *the testId
runId, *specific run for the test
pId,
value

results
rId,
rName,
rDesc

test_results
tId, *the testId
runId, *specific run for the test
rId,
value

Something like that... its rough I know but its what I got.

Corey

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-16 : 10:41:00

MuadDBA

628 Posts

Posted - 2004-06-16 : 13:59:15

What you really need to do is hire a DBA to do your database stuff instead of letting scientists do it. I know scientists are all super-educated and stuff, but the findamentals of a lot of this stuff isn't glaringly obvious to someone with little to no experience in it....it would be like using a scientist to figure out why your car is running poorly. At some point, he might be able to tell you that your air-fuel ratio is sub-optimal, but he wouldn't realize you've got a hole in your intake system after the air is metered unless he knew to look for that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources