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.
| Author |
Topic |
|
inbs
Aged Yak Warrior
860 Posts |
 Posted - 2010-08-12 : 01:23:10
|
| i have two tablesORDERS(4 million rows) ORDERLIMES (9 million rows)1.SELECT ORDERS.OrderNo,ORDERS.ORDERDATE,ORDERLIMES.ROWFROM ORDERS JOIN ORDERLIMES ON ORDERS.ORDERNO=ORDERLIMES.ORDERNOWHERE ORDERS.ORDERDATE>='2009-01-01'2.SELECT ORDERS.OrderNo,ORDERS.ORDERDATE,ORDERLIMES.ROWFROM ORDERS JOIN ORDERLIMES ON (ORDERS.ORDERNO=ORDERLIMES.ORDERNO AND ORDERLIMES.LINEDATE>='2009-01-01')WHERE ORDERS.ORDERDATE>='2009-01-01what is thee best way? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-12 : 02:48:31
|
| Well ... they give different results, so it depends what you want.if they don't give different results, and ORDERLIMES.LINEDATE is always the same as ORDERS.ORDERDATE then it would be better to drop the ORDERLIMES.LINEDATE column to remove the data duplication (and thus the possibility that the values they contain could become different).#2 provides more constraints on the data, which may enable the optimiser to choose a different index etc. and may improve performance |
 |
|
|
|
|
|
performace