Databases
StackOverflow2013
Postsdbo
POSQL Server Performance With Large Query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server Performance With Large Query
    primarykey
    data
    text
    <p>Hi everyone I have a couple of queries for some reports in which each query is pulling Data from 35+ tables. Each Table has almost 100K records. All the Queries are Union ALL for Example</p> <pre><code>;With CTE AS ( Select col1, col2, col3 FROM Table1 WHERE Some_Condition UNION ALL Select col1, col2, col3 FROM Table2 WHERE Some_Condition UNION ALL Select col1, col2, col3 FROM Table3 WHERE Some_Condition UNION ALL Select col1, col2, col3 FROM Table4 WHERE Some_Condition . . . And so on ) SELECT col1, col2, col3 FROM CTE ORDER BY col3 DESC </code></pre> <p>So far I have only tested this query on Dev Server and I can see It takes its time to get the results. All of these 35+ tables are not related with each other and this is the only way I can think of to get all the Desired Data in result set. </p> <ol> <li><p>Is there a better way to do this kind of query ??<br/> </p></li> <li><p>If this is the only way to go for this kind of query how can I improve the performance for this Query by making any changes if possible?? <br/></p></li> </ol> <p><strong>My Opinion</strong> <br/> I Dont mind having a few dirty-reads in this report. I was thinking of using <strong>Query hints</strong> <code>with nolock</code> or <code>Transaction Isolation Level</code> set to <code>READ UNCOMMITED</code>.</p> <p>Will any of this help ???</p> <p><strong>Edit</strong><br/> Every Table has 5-10 Bit columns and a Corresponding Date column to each Bit Column and my condition for each SELECT Statement is something like </p> <pre><code>WHERE BitColumn = 1 AND DateColumn IS NULL </code></pre> <p><strong>Suggestion By Peers</strong><br/></p> <p><strong>Filtered Index</strong></p> <pre><code>CREATE NONCLUSTERED INDEX IX_Table_Column ON TableName(BitColumn) WHERE BitColum = 1 </code></pre> <p><strong>Filtered Index with Included Column</strong></p> <pre><code>CREATE NONCLUSTERED INDEX fIX_IX_Table_Column ON TableName(BitColumn) INCLUDE (DateColumn) WHERE DateColumn IS NULL </code></pre> <p><strong>Is this the best way to go ? or any suggestions please ???</strong></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USM.Ali
    1. USM.Ali
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server Performance With Large Query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server Performance With Large Query
      1. USM.Ali
      1. VTBountyStart
    3. VO
      singulars
      1. POSQL Server Performance With Large Query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy you need this kind of query?
      singulars
      1. POSQL Server Performance With Large Query
      1. USHamlet Hakobyan
    2. COAre you sure the tables aren't related? Hard to see how you could union completely un-related data.
      singulars
      1. POSQL Server Performance With Large Query
      1. USAndrew
    3. COHave you had a look at the execution plan for the query? Are appropriate indexes available for each of the 35+ tables?
      singulars
      1. POSQL Server Performance With Large Query
      1. USIan Nelson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload