0

I'm writing a query to grab 50 columns from eight tables. Just out of curiosity is there a better way performance wise to grab that data rather than using a series of inner join Table A on Table B.orderNumber = Table A.OrderNumber for Tables A-H?

The database this is running against is SQL Server 2008.

Here is the initial query I'm still writing to:

SELECT
    /*Buyer and Seller information for order number */
   [QCV_BuyerSellers].[OrderNumber] AS OrderNum
  ,[QCV_BuyerSellers].[OrderGuid] AS Order_GUID
  ,[QCV_BuyerSellers].[Buyer1_EntityTypeId] AS ENT_TYPE
  ,[QCV_BuyerSellers].[Buyer1_EntityTypeName] AS ENT_TYPE_NAME
  ,[QCV_BuyerSellers].[Buyer1_FullName] AS FULL_NAME
  ,[QCV_BuyerSellers].[Buyer1_FirstName] AS BF_Name
  ,[QCV_BuyerSellers].[Buyer1_MiddleName] AS BM_Name
  ,[QCV_BuyerSellers].[Buyer1_LastName] AS BL_Name
  ,[QCV_BuyerSellers].[Buyer1_TIN] AS B_Tin1
  ,[QCV_BuyerSellers].[Buyer1_PhoneHome] AS B_PhoneHome
  ,[QCV_BuyerSellers].[Buyer1_PhoneWork] AS B_PhoneWork

  ,[QCV_BuyerSellers].[Buyer2_FullName] AS FULL_NAME2
  ,[QCV_BuyerSellers].[Buyer2_FirstName] AS BF_Name2
  ,[QCV_BuyerSellers].[Buyer2_MiddleName] AS BM_Name2
  ,[QCV_BuyerSellers].[Buyer2_LastName] AS BL_Name2
  ,[QCV_BuyerSellers].[Buyer2_TIN] AS B_Tin2
  ,[QCV_BuyerSellers].[Buyer2_PhoneHome] AS B_PhoneHome2
  ,[QCV_BuyerSellers].[Buyer2_PhoneWork] AS B_PhoneWork2

  ,[QCV_BuyerSellers].[Seller1_FirstName] AS SF_Name
  ,[QCV_BuyerSellers].[Seller1_MiddleName] AS SM_Name
  ,[QCV_BuyerSellers].[Seller1_LastName] AS SL_Name
  ,[QCV_BuyerSellers].[Seller1_TIN] AS S_Tin
  ,[QCV_BuyerSellers].[Seller1_PhoneHome] AS S_PhoneHome
  ,[QCV_BuyerSellers].[Seller1_PhoneWork] AS S_PhoneWork

  ,[QCV_BuyerSellers].[Seller2_FirstName] AS SF_Name2
  ,[QCV_BuyerSellers].[Seller2_MiddleName] AS SM_Name2
  ,[QCV_BuyerSellers].[Seller2_LastName] AS SL_Name2
  ,[QCV_BuyerSellers].[Seller2_TIN] AS S_Tin2
  ,[QCV_BuyerSellers].[Seller2_PhoneHome] AS S_PhoneHome2
  ,[QCV_BuyerSellers].[Seller2_PhoneWork] AS S_PhoneWork2

  /*OMFILE Property table fields by order number */
  ,[OMFILE_PROPERTY].[PropertyAddress1] AS Prop_Adress
  ,[OMFILE_PROPERTY].[PropertyCity] AS Prop_City
  ,[OMFILE_PROPERTY].PropertyCounty AS Prop_County
  ,[OMFILE_PROPERTY].PropertyState AS Prop_State 
  ,[OMFILE_PROPERTY].PropertyZip AS Prop_Zip
  ,[OMFILE_PROPERTY].PropertyBriefLegal1 AS Prop_Brief1
  ,[OMFILE_PROPERTY].PropertyBriefLegal2 AS Prop_Brief2

  ,[OMEXT2_SUBDIVISION].SubdPUDFlag AS SD_PUD_FLAG
  ,[OMEXT2_SUBDIVISION].SubdCondominiumFlag AS SD_Condo_Flag

   /*OMFILE Payoff Fields for order number */
  ,[OMFILE_PAYOFFS].[Payoff1Name] 
  ,[OMFILE_PAYOFFS].[Payoff1LoanNumber] 
  ,[OMFILE_PAYOFFS].[Payoff1Phone] 

  ,[OMFILE_PAYOFFS].[Payoff2Name] 
  ,[OMFILE_PAYOFFS].[Payoff2LoanNumber] 
  ,[OMFILE_PAYOFFS].[Payoff2Phone]

  /*Loan Number & Amount From  OMFILE_LENDERLOAN table */
  ,[OMFILE_LENDERLOAN].[LoanNumber]  
  ,[OMFILE_LENDERLOAN].[LoanAmount] 

 FROM [REO].[dbo].[V_BuyerSellers] 
  INNER JOIN [REO].[dbo].[OMFILE_PROPERTY] 
  on V_BuyerSellers_Flat.OrderNumber = OMFILE_PROPERTY.OrderNumber
  INNER JOIN 
  [REO].[dbo].[OMEXT2_SUBDIVISION]
  on [REO].[OrderNumber] = [OMEXT2_SUBDIVISION].[OrderNumber]
  INNER JOIN [REO].[dbo].[OMFILE_PAYOFFS] 
  on [OMFILE_PROPERTY].[OrderNumber] = [OMFILE_PAYOFFS].[OrderNumber] 
  INNER JOIN [REO].[dbo].[OMFILE_LENDERLOAN] 
  on [OMFILE_PAYOFFS].[OrderNumber] = [OMFILE_LENDERLOAN].[OrderNumber] 

  WHERE [QCV_BuyerSellers].[OrderNumber] = 'QCT-8735410'
Alex
  • 443
  • 3
  • 18
  • Probably not, but you should post information about your tables structure and the query you are using to join for better feedback – Sam Cohen-Devries Sep 28 '15 at 15:08
  • Agree with @Samcd. There might be cases where if some of those tables are small enough, and stable enough, and you query them often enough, it might be profitable to load the entire table into your program and just keep them there for multiple DB queries. But quite likely the DB server would do a much better job at caching and optimizing than you ever could - assuming you have created indexes that make those joins efficient.This last is VERY important. Do you have those indexes? –  Sep 28 '15 at 15:10
  • No to my best knowledge, however order of joins depending on data in respective tables can help you get better performance in certain scenarios, have a look at http://www.sql-server-performance.com/2006/tuning-joins/ – Vinay Pandey Sep 28 '15 at 15:13
  • This could be a scenario where creating an indexed view of the results of your query gives a performance improvement. This Stack Overflow answer has more information on Indexed Views in SQL Server - http://stackoverflow.com/questions/3986366/how-to-create-materialized-views-in-sql-server – bunnmatt Sep 28 '15 at 15:38
  • If you deal with very large tables and you know, that you'll need just a few rows (pre-selection) a CTE might be a good idea. If you need sub-data only in sepcial conditions an APPLY could give a better plan. But - in most cases! - one should not try to clever the optimizer out ;-) The implicit usage of indexes and statistics, caching and re-structuring will be close to optimal in most cases... – Shnugo Sep 28 '15 at 15:43
  • One hint, which has nothing to do with your question ;-) : I'd use shorter alias names :-) – Shnugo Sep 28 '15 at 15:46

2 Answers2

0

I'd recommend starting at running the query with 'Include Actual Execution Plan' (Ctrl-M toggles this on/off) and looking for missing indexes that could improve performance. Your query doesn't have any obvious flaws that could be re-factored. It looks like you're using a view already, you could make a new view from this query and index it, but it's no guarantee that an indexed view will perform better than a view that references indexed tables, typically the indexes on the views underlying tables are still utilized.

There are ways to avoid the performance hit from joining multiple tables, but they have their drawbacks. You could create a table from this query without the WHERE and index the OrderNumber field. That's kind of a dark path to go down if you aren't ready to implement a proper data warehouse, because you'll likely end up with a lot of one-off reporting tables and depending on the environment it might need to be updated at unreasonable intervals.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
-1

In general, JOINS gives better performance and readability than using the old school way, i.e : WHERE used on PK = FK.

I'd stick with the JOINS.

If performance is a concern, I suggest you use SQL Profiler for a complete analysis.

I would also suggest writing a view to get better performances on SELECT of your complex query.

Dave
  • 2,774
  • 4
  • 36
  • 52
  • Ok people, if you are going to downvote an answer, you owe it to the entire audience to EXPLAIN your downvote. – Juan-Carlos Sep 28 '15 at 15:15
  • Well, shoulda just answered No. in a comment just like the others hehe. – Dave Sep 28 '15 at 15:24
  • I didn't down vote, but the link between your answer and the question is not clear enough for my taste. E.g., suggesting writing a view can be interpreted many ways, some which would have a negative impact on performance. Also, referencing the `WHERE` clause is rather bizarre, considering the only joins in the sample query are simple one key, and it was not mentioned by OP. – Adam Martin Sep 28 '15 at 16:13
  • @AdamMartin Where clause can be used as an alternative to sql join. It is not recommanded. But it still is. http://it.toolbox.com/blogs/data-analytics/alternative-method-to-perform-complex-sql-join-query-60894 – Dave Sep 28 '15 at 17:40
  • I understand that it can be used, it's just bizarre to suggest since the OP wants improvements... – Adam Martin Sep 28 '15 at 17:45
  • @AdamMartin I was stating an alternative. The alternative is not as good, as far as I know, than joins. What is bizzare here is the hate to my answer that states the same elements as every other comments and answers. probably not written clearly enough. Stackoverflow is an hostile environement now a day. – Dave Sep 28 '15 at 17:59