BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
Yngeinstn
Community Trekker

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 
			ON  ( t5.header_id = t2.header_id )  
		LEFT OUTER JOIN table_3 t3 
			ON  ( t3.header_id = t2.header_id )  
		LEFT OUTER JOIN table_4  t4 
			ON  ( t4.header_id = t2.header_id )  
	WHERE  (  ( t1.wafer_number IN  ( 'wafer_1' ,  'wafer_2' ,  'wafer_3' )  ) )
		AND ( t2.testname IN ( 'test_1', 'test_2', 'test_3' ) )
0 Kudos
4 REPLIES 4
pmroz
Super User

Re: SQL - Very Large Data Table

ODBC tends to be slow.  You could try:

 

  • Running your query in a tool like PL/SQL Developer, SQL Navigator or Toad.  These products have faster access to Oracle
  • 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.

0 Kudos
Yngeinstn
Community Trekker

Re: SQL - Very Large Data Table

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

0 Kudos
Yngeinstn
Community Trekker

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
0 Kudos
pmroz
Super User

Re: SQL - Very Large Data Table

Faster CPU and more RAM will always help.

0 Kudos