Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level IV

## SQL - Very Large Data Table

I have exhausted my search or understanding how to resolve our problem performing an SQL query in a more effecient manner.

The included SQL script pulls from 3 tables that have combined rows that equal 40,970,221. This of course for 3 wafers but you can divide that by 3 and that is my standard data pull.

Any suggestions if there are any? Am i doomed to have to endure the time it takes to pull this all in.

``````    SELECT
CASE
WHEN t2.testname = 'test_1' THEN t3.ts
WHEN t2.testname = 'test_2' THEN t4.ts
WHEN t2.testname = 'test_3' THEN t5.ts
END AS TimeStamp,
t1.testdate,
t1.wafer_number,
t2.rownum,
t2.colnum,
t2.subrow,
t2.subcol,
t2.part_name,
t2.part_number,
t2.testname,
t2.set_id,
t2.trmode,
t2.channel,
t2.teststate,
t2.refstate,
t2.filterstate,
t2.data_id5,
CASE
WHEN t2.testname = 'test_1' THEN t3.input_1
WHEN t2.testname = 'test_2' THEN t4.input_2
WHEN t2.testname = 'test_3' THEN t5.input_3
END AS INPUT
t3.output_1
t3.output_2,
t3.output_3,
t4.output_1,
t4.output_2,
t4.output_3,
t5.output_1,
t5.output_2
FROM tst_master  t1
LEFT OUTER JOIN table_2 t2
ON  ( t2.run_id = t1.run_id )
LEFT OUTER JOIN table_5 t5
LEFT OUTER JOIN table_3 t3
LEFT OUTER JOIN table_4  t4
WHERE  (  ( t1.wafer_number IN  ( 'wafer_1' ,  'wafer_2' ,  'wafer_3' )  ) )
AND ( t2.testname IN ( 'test_1', 'test_2', 'test_3' ) )``````
4 REPLIES 4
Highlighted
Super User

## Re: SQL - Very Large Data Table

ODBC tends to be slow.  You could try:

• Export the data to a .CSV file
• Import the CSV file into JMP

If you have SQL server there are similar products; just not familiar with any of them.

Highlighted
Level IV

## Re: SQL - Very Large Data Table

Thanks for the reply... That is an interesting idea. I am going to try it

Highlighted
Level IV

## Re: SQL - Very Large Data Table

Quick question. Would i gain anything but upgrading my computer to a more beefed up system or am i still limited to whatever is causing the lag?

Thanks
Highlighted
Super User

## Re: SQL - Very Large Data Table

Faster CPU and more RAM will always help.

Article Labels

There are no labels assigned to this post.