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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-07-25 : 09:01:57
|
| Reet writes "Hi,I have written a view but it is taking so long to create that my ASP application takes forever to display the required data.I know it is because of all the joins I am doing but I really don't know how to re-write it so that it will process more quickly.Please can anyone suggest anything to help me? The sql is as follows:SELECT TOP (100) PERCENT OMS.ID, OMS.omOrderDate, OMS.omPart, OMS.omDesc, OMS.omOrderNumber, OMS.omCustomerID, OMS.omQuantity, OMS.omCostPrice, OMS.omSellPrice, OMS.omDistStock, OMS.omDistID, OMS.omPO, OMS.omETA, OMS.omPriority, OMS.omShipdate, OMS.omConsignment, OMS.omNotes, cust.name, cust.lastName, sup.supplierName, Status.statusName, OMS.omStatusID, prod.Manufacturers, cust.email, prod.idProduct, OMSps.costPrice, OMSps.stockLevel, OMSps.supplierID, sup2.supplierName AS spSupplierNameFROM OMSystem AS OMS LEFT OUTER JOIN dbo.suppliers AS sup ON OMS.omDistID = sup.idSupplier INNER JOIN dbo.customers AS cust ON OMS.omCustomerID = cust.idcustomer INNER JOIN Status ON OMS.omStatusID = Status.statusID INNER JOIN dbo.products AS prod ON OMS.omPart = prod.MFID COLLATE Latin1_General_CI_AS LEFT OUTER JOIN OMS_ProductSupplier AS OMSps ON OMS.omPart = OMSps.partCode LEFT OUTER JOIN dbo.suppliers AS sup2 ON OMSps.supplierID = sup2.idSupplierORDER BY OMS.omOrderNumber DESCMany thanksReet" |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-25 : 09:17:36
|
| Hi Reet , Welcome to SQL Team!You need to review the Query Plan for your query and see whether there are areas of significant "logical" [NOT "physical"] I/O and table-scans that indicate that indexes are not being used etc., and add indexes as appropriate.You should also prefix all tables with "dbo." as this helps SQL Server cache the query plan (you've done that on all-bar-two I think, so you are nearly there on that one!)Your "COLLATE Latin1_General_CI_AS" may very well be the killer - I expect that will cause a table scan. Can you not make those two columns use the same collation, rather than having to force it?I'm not keen on using "TOP 100 PERCENT" to force the sort order of a VIEW. Better that the final query does that, rather than it relying on the VIEW to do it.Kristen |
 |
|
|
|
|
|
|
|
optimizing views