Geek Answers Handbook

High Query SQL Server Performance

Hi everyone, I have several queries for some reports in which each query retrieves data from 35+ tables. Each table has almost 100 thousand records. Example:

;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

So far, I have only tested this query on Dev Server, and I see that it takes time to get the results. All these 35+ tables are not related to each other, and this is the only way with which you can get all the required data in the result set.

  • Is there a better way to make such a request?

  • If this is the only way to search for such a query, how can I improve the performance for this Query by making any changes, if possible ??


. with nolock Transaction Isolation Level READ UNCOMMITED.

?

Edit
5-10 , SELECT - -

WHERE BitColumn = 1 AND DateColumn IS NULL

peers

CREATE NONCLUSTERED INDEX IX_Table_Column
ON TableName(BitColumn)
WHERE BitColum = 1


CREATE NONCLUSTERED INDEX fIX_IX_Table_Column
ON TableName(BitColumn)
INCLUDE (DateColumn)
WHERE DateColumn IS NULL

? , .

+4
4

, , . , UNION, :

  • , ,
    • ? , ? , (INSERT ..) .
    • -? , ?
    • ?
  • . WHERE,
    • , , - , WHERE /, , (.. )
  • ORDER BY
    • , - ( )
    • ( /, ). , ? 3 . , SQL_EBCDIC280_CP1_CS_AS, SQL_Latin1_General_CP1251_CS_AS, SQL_Latin1_General_CP1_CI_AS . , , .
    • " " , SELECT - 32 767 , ( ) . , , .NET SqlConnection . SQL Server , . ,
    • , SQL Server; TCP/IP
    • , , , .

...

, , .., , , SQL Server 2014 :-),... , .

, , , .

,

+2

, , , , . ? 100K , . 10000 5 , , , - . , , ​​ , . .

BTW, CTE, . , Temp DB . LDF MDF 10%. , , MDF LDF . Covered filter include col1, col2 co3, Date, Date .

35 ? max , Indexed-Views . , , , Indexed-View.

, 35. , . insert/update/delete 35, , , insert/update/delete . , , , . Date - Null-Able, , " WHERE Date NULL". , , - , Date NULL, , BIT col1, col2, col3 ..... , .

+1

. , , .

: Where, (WHERE BitColumn = 1 AND DateColumn IS NULL) , , , , BitColum DateColumn, select, " " .

CREATE NONCLUSTERED INDEX IX_[Choose an IndexName]
ON TableName(BitColumn, DateColumn)
INCLUDE (col1, col2, col3)

, , , , WHERE BitColumn = 1 AND DateColumn IS NULL.

View CTE:

CREATE VIEW [Choose a ViewName]
AS
(
Select col1, col2, col3 FROM Table1 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE Some_Condition
.
.
.
)

, 35 , , clausule , 35 , .

: " 5-10 ". , -. , BitColums DateColums, :

ALTER TABLE Table1 ADD ComputedFilterFlag AS
CAST(
    CASE WHEN BitColum1 = 1 AND DateColumn1 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN BitColum2 = 1 AND DateColumn2 IS NULL THEN 2 ELSE 0 END +
    CASE WHEN BitColum3 = 1 AND DateColumn3 IS NULL THEN 4 ELSE 0 END
AS tinyint)

2 ^ (X-1) X (ColumnX = 1 DateColumnX IS NOT NULL). , . 3, , : 1, 1 2, Date2. ComputedFilterFlag, ComputedFilterFlag . 8 , tinyint, -.

ComputeFilterFlag colum:

CREATE NONCLUSTERED INDEX IX_[Choose an IndexName]
ON TableName(ComputedFilterFlag)
INCLUDE (col1, col2, col3)

:

CREATE VIEW [Choose a ViewName]
AS
(
Select col1, col2, col3 FROM Table1 WHERE ComputedFilterFlag IN [Choose the Target Filter Value set]--(1, 3, 5, 7)
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE ComputedFilterFlag IN [Choose the Target Filter Value set]--(1, 3, 5, 7)
.
.
.
)

, , 35 .

, , .

+1

, , . , .

.

, . , . , , , , . - , , , .

+1

More articles:


All Articles