Avoid blindly pursuing a sentence in SQL Server

Server

Problem description

Business needs are as follows:

There are tables A and B, the two tables are structurally consistent, for different business services, now write a stored procedure, the stored procedure accepts a parameter, when the parameter is 0 o'clock, query table A, parameter is 1 o'clock, query table B.

A, the general processing method

IF @Flag = 0

SELECT * FROM dbo. A

ELSE IF @Flag = 1

SELECT * FROM dbo. B

B, a sentence of the processing method

SELECT * FROM dbo. A

WHERE @Flag = 0

UNION All

SELECT * FROM dbo. B

WHERE @Flag = 1

Analysis

Judging from the simplicity of the sentence, method B is skillful, and between them, which is better? You may evaluate the performance to determine which one to use. Simply from the statement, it seems that the efficiency of the two is similar, the following data test to reflect the result seems to be the same as imagined

Establish a test environment (note, this test environment is for several topics, so the structure looks a little weird)

Use tempdb

Go

SET NOCOUNT on

--======================================

--Create a test environment

--======================================

RAISERROR (' Create test Environment ', 1) with nowait

--Table A

CREATE TABLE [dbo]. A

[Trannumber] [INT] IDENTITY (1, 1) not NULL,

[Invno] [Char] (8) Not NULL,

[ITEM] [Char] () NULL DEFAULT ('),

PRIMARY KEY ([Trannumber])

)

CREATE INDEX [Indexoninvno] on [dbo]. A ([Invno])

CREATE INDEX [Indexonitem] on [dbo]. A ([ITEM])

CREATE INDEX [indexoniteminnvo] on [dbo]. A ([Invno], [ITEM])

Go

--Table B

CREATE TABLE [dbo]. B

[ItemNumber] [Char] () not NULL DEFAULT ('),

[Companycode] [Char] (4) Not NULL,

[Ownercompanycode] [Char] (4) NULL,

PRIMARY KEY ([ItemNumber], [Companycode])

)

CREATE INDEX [ItemNumber] on [dbo]. B ([ItemNumber])

CREATE INDEX [Companycode] on [dbo]. B ([Companycode])

CREATE INDEX [Ownercompanycode] on [dbo]. B ([Ownercompanycode])

Go

--======================================

--Generate test data

--======================================

RAISERROR (' Generate test Data ', 1) with nowait

INSERT [dbo]. A ([Invno], [ITEM])

SELECT Left (NEWID (), 8), right (NEWID (), 15)

From syscolumns A, syscolumns B

INSERT [dbo]. B ([ItemNumber], [Companycode], [Ownercompanycode])

SELECT Right (NEWID (), on), left (NEWID (), 4), Left (NEWID (), 4)

From syscolumns A, syscolumns B

Go

Perform performance testing

DECLARE @a int

SET @a = 1

DECLARE @t TABLE (

ID int IDENTITY,

a int, b int)

DECLARE @dt datetime, @loop int, @id int

SET @loop = 0

While @loop < 5

BEGIN

SET @loop = @loop + 1

RAISERROR (' Test%d ', 1, @loop) with nowait

SET @dt = GETDATE ()

SELECT [ITEM] from A

WHERE @a = 0

and [ITEM] < ' A '

UNION All

SELECT [ItemNumber] from B

WHERE @a = 1

and [ItemNumber] < ' A '

INSERT @t (a) VALUES (DATEDIFF (MS, @dt, GETDATE ()))

SELECT @id = scope_identity (), @dt = GETDATE ()

IF @a = 0

SELECT [ITEM] from A

WHERE [ITEM] < ' A '

ELSE IF @a = 1

SELECT [ItemNumber] from B

WHERE [ItemNumber] < ' A '

UPDATE @t SET B = DATEDIFF (MS, @dt, GETDATE ())

WHERE id = @id

End

SELECT * from @t

UNION All

SELECT NULL, SUM (a), sum (b) from @t

Performance Test Results

ID a B

--- ------- -------

1 3410 2063

2 1703 1656

3 1763 1656

4 1800 1793

5 1643 1856

NULL 10319 9024

From the results, the performance difference between the two is very small, so the performance of the two comparisons can be seen as no difference

Where the problem lies

Although there is no difference in performance, there is another problem that you may never have considered, that is the question of access to the table, in method A, you will definitely only access one table, and in method B, is this still the case? The answer is no, method B will always scan two tables. The subtext is that even in my query, only a table is used, but if Table B is locked, the entire query is blocked, and method A does not.

To prove the problem, we'll do the following test

Block test-for Table a lock (query window a)

BEGIN TRAN

UPDATE A SET [ITEM] = right (NEWID (), 4)

WHERE [ITEM] BETWEEN ' 9 ' and ' A '

--rollback TRAN--Do not roll back the transaction so that the lock remains

Block test-Test Query method A (query window B)

--Run Query Windows 2

DECLARE @a int

SET @a = 1

IF @a = 0

SELECT [Trannumber] from A

WHERE [ITEM] < ' A '

ELSE IF @a = 1

SELECT [ItemNumber] from B

WHERE [ItemNumber] < ' A '

Block test-Test Query Method B (Query window C)

--Run Query Windows 3

DECLARE @a int

SET @a = 1

SELECT [ITEM] from A

WHERE @a = 0

and [ITEM] < ' A '

UNION All

SELECT [ItemNumber] from B

WHERE @a = 1

and [ItemNumber] < ' A '

Results

You will see that query window B in the query will be completed in a timely manner, and Query window C query will be waiting, you can execute stored procedure Sp_who2, see the current block status to determine whether Query window C query is query window a query block live

Conclusion

Do not use Query method B, it looks great, the actual result is to increase the chance of being block

trackback:http://tb.blog.csdn.net/trackback.aspx?postid=787074

[Click here to collect this article] posted on June 10, 2006 20:55:00

The sea laughs and publishes in 2006-06-11 00:37:00 ip:221.221.210.*
Incisive!
Thanks for sharing!

HMJ published in 2006-06-11 13:18:00 ip:222.95.184.*
and learned something new!

cyz1980 published in 2006-06-12 08:15:00 ip:222.76.2.*
Brother Zou:
Hello, the above description of the problem I have encountered, to my inspiration is also very large, thank you. But in practice, some still want to "coherent". such as the following questions (the code is longer, because of this, it is more profound experience, haha.) To understand the location of the place look brother Zou pointing:
The first method (for view, easy database migration, easy access, such as quick calls, wide applicability):
DECLARE @month datetime
Set @month = ' 2005-4-1 '
Select @month as month, dpname1 as department, ISNULL (account number, 0) as account number, ISNULL (first deposit after opening, 0) as the first deposit after opening, isnull (consumption amount, 0) as consumption amount,
IsNull (consumption times, 0) as consumption times, isnull (access amount, 0) as Access amount, isnull (deposit and withdrawal times, 0) as deposit and withdrawal times, ISNULL (total card balance, 0) as Total balance
From (select distinct dpcode1,dpname1 to t_department) Department left outer join (select DpCode1, Kh_month, COUNT (*) as Account number, SUM (in_out_fare)
As the first deposit after opening
From (SELECT Dep. DpCode1, RTRIM (CAST (T_customers.opendt) as Char)
+ '-' + RTRIM (CAST (MONTH (T_customers.opendt) as Char)
+ '-' + RTRIM (day (0)) as Kh_month, Min_in_out_fare.in_out_fare
From T_customers INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 as Dpcode, DpCode1
From T_department) DEP on
T_customers.account = Dep.dpcode LEFT outer JOIN
(SELECT Min_opcount. CustomerID,
T_cashrec.infare-t_cashrec.outfare In_out_fare
From (SELECT CustomerID, MIN (opcount) as Min_opcount
From T_cashrec
GROUP by CustomerID) Min_opcount INNER JOIN
T_cashrec on
Min_opcount. CustomerID = T_cashrec.customerid and
Min_opcount.min_opcount = T_cashrec.opcount) Min_in_out_fare on
Min_in_out_fare. CustomerID = T_customers.customerid)
First-level unit monthly Account Details
GROUP by DpCode1, Kh_month has kh_month= @month/* First-level unit monthly Account Summary * *
KH on kh.dpcode1=department.dpcode1 left outer join (SELECT DpCode1, Xf_month, SUM (opfare) as consumption amount, COUNT (*) as consumption
From (SELECT Dep. DpCode1, RTRIM (Consumerec. OPDT) as Char)
+ '-' + RTRIM (CAST (MONTH) (Consumerec. OPDT) as Char) + '-' + RTRIM (day (0))
As Xf_month, Consumerec. Opfare
From T_consumerec Consumerec INNER JOIN
T_customers on
Consumerec. CustomerID = T_customers.customerid INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 as Dpcode, DpCode1
From T_department) dep on t_customers.account = Dep.dpcode)
Monthly consumption details of the first level unit
GROUP by DpCode1, Xf_month has xf_month= @month/* First-level unit monthly Consumption Summary * *
XF on xf.dpcode1=department.dpcode1 left outer join (SELECT DpCode1, Cqk_month, sum (infare-outfare) as Access amount, COUNT (*) a s deposit and withdrawal times
From (SELECT Dep. DpCode1, RTRIM (CAST (CONSUMEREC.CASHDT) as Char)
+ '-' + RTRIM (CAST (MONTH (CONSUMEREC.CASHDT) as Char)
+ '-' + RTRIM (day (0)) as Cqk_month, Consumerec.infare,
Consumerec.outfare
From T_cashrec Consumerec INNER JOIN
T_customers on
Consumerec. CustomerID = T_customers.customerid INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 as Dpcode, DpCode1
From T_department) dep on t_customers.account = Dep.dpcode)
Monthly deposit and withdrawal details of level one unit
GROUP by DpCode1, Cqk_month has cqk_month= @month/* First-level unit monthly deposit and withdrawal Summary * *
CQ on the Cq.dpcode1=department.dpcode1 left outer join (SELECT Dep. DpCode1, SUM (id_maxo.oddfare) as card balance Total
From (SELECT Id_m_maxc.customerid, Id_c_o.oddfare
From (SELECT CustomerID, MAX (Opcount) as Max_opcount
From (SELECT CustomerID, Opcount, RTRIM (CAST (Dt) as Char)
+ '-' + RTRIM (CAST (MONTH (Dt) as char) + '-' + RTRIM (day (0))
As month
From (SELECT CustomerID, Opcount, OPDT as DT
From T_consumerec
UNION All
SELECT CustomerID, Opcount, CASHDT as DT
From T_cashrec
UNION All
SELECT CustomerID, Opcount, putoutdt as DT
From T_subsidyputout) id_c_d id_c_m where month <= @month/* Month parameter * *
GROUP by CustomerID
) Id_m_maxc INNER JOIN
(SELECT CustomerID, Opcount, Oddfare
From (SELECT CustomerID, Opcount, Oddfare
From T_consumerec
UNION All
SELECT CustomerID, Opcount, Oddfare
From T_cashrec
UNION All
SELECT CustomerID, Opcount, Oddfare
From T_subsidyputout) lid_c_o) Id_c_o on
Id_c_o.customerid = Id_m_maxc.customerid and
Id_c_o.opcount = id_m_maxc.max_opcount) Id_maxo INNER JOIN
T_customers on id_maxo.customerid = T_customers.customerid INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 as Dpcode, DpCode1
From T_department) dep on t_customers.account = dep.dpcode/* level of card balances for a certain month
GROUP by Dep. DPCODE1/* First level unit in a month of the card balance Summary */) Kye on Kye.dpcode1=department.dpcode1

Sample data after execution:

Monthly account number of the first deposit after opening account consumption amount of access to the amount of deposits and withdrawals card balance total
2005-4-1 Employee Card 4￥2,400.00￥7,728.29 1054￥531,369.40 1112￥523,937.84
2005-4-1 Employee Card 2 0￥0.00￥0.00 0￥0.00 0￥0.00
2005-4-1 Foreign unit personnel 100￥620.00￥0.00 0￥620.00 4￥620.00
2005-4-1 Card 0￥0.00￥0.00 0￥0.00 0￥0.00
2005-4-1 Cash Card 2￥0.00￥0.00 0￥0.00 0￥0.00
2005-4-1 Discount Card 56￥16,500.00￥984.40 152￥16,500.00 55￥15,515.60
2005-4-1 Group Agent card 0￥0.00￥0.00 0￥0.00 0￥0.00

The second method [encapsulates a stored procedure and uses a large number of temporary tables (efficiency?). To facilitate reading comprehension and updating, but with limited scope of application):
DECLARE @month datetime
Set @month = ' 2004-9-1 '

SELECT CustomerID, Opcount, fare, oddfare, DT, RTRIM (CAST (year (DT) as Char))
+ '-' + RTRIM (CAST (MONTH (DT) as char) + '-' + RTRIM (day (DT)) as RQ,
RTRIM (CAST (year (DT) as char)) + '-' + RTRIM (CAST (MONTH (DT) as Char))
+ '-' + RTRIM (day (0)) as [month], category into #mingxi
From (SELECT CustomerID, Opcount, Opfare fare, oddfare, OPDT dt, ' consume ' as category
From T_consumerec
UNION All
SELECT CustomerID, Opcount, Infare-outfare fare, oddfare, CASHDT DT,
' Cashier ' as category
From T_cashrec) L

SELECT T_customers.customerid, T_dpcode. DpCode1 into #custid_dpcode1
From (SELECT DpCode1, DpCode1 + DpCode2 + DpCode3 as Dpcode
From t_department) T_dpcode INNER JOIN
T_customers on t_dpcode.dpcode = T_customers.account

SELECT Custid_dpcode1. DpCode1, COUNT (*) as account number, SUM (L.in_out_fare)
as the first deposit after opening into #kh
from (SELECT T_customers.customerid, RTRIM (CAS T (T_customers.opendt)
as char)) + '-' + RTRIM (CAST (MONTH (T_customers.opendt) as char)
+ '-' + RTRIM (day (0)) As [month], ISNULL ([First].in_out_fare, 0)
as In_out_fare
from t_customers left OUTER JOIN
(SELECT min_opcount . CustomerID,
T_cashrec.infare-t_cashrec.outfare as In_out_fare
from (SELECT CustomerID, MIN (opcount) as Min_opco UNT
from T_cashrec
GROUP by CustomerID) min_opcount INNER JOIN
T_cashrec on
Min_opcount. CustomerID = T_cashrec.customerid and
Min_opcount.min_opcount = T_cashrec.opcount) [a] on
T_ Customers.CustomerID = [A]. CustomerID) L INNER JOIN
#custid_dpcode1 custid_dpcode1 on l.customerid = Custid_dpcode1. CustomerID
WHERE (l.[month] = @month)
GROUP by Custid_dpcode1. DpCode1

SELECT Custid_dpcode1. DpCode1, SUM (mingxi.fare) as Access amount, COUNT (*)
As deposit and withdrawal number into #cq
From #mingxi Mingxi INNER JOIN
#custid_dpcode1 custid_dpcode1 on Mingxi. CustomerID = Custid_dpcode1. CustomerID
WHERE (mingxi. Category = ' cashier ') and (mingxi.[ Month] = @month)
GROUP by Custid_dpcode1. DpCode1

SELECT Custid_dpcode1. DpCode1, SUM (mingxi.fare) As consumption amount, COUNT (*)
As consumption number into #xf
From #mingxi Mingxi INNER JOIN
#custid_dpcode1 custid_dpcode1 on Mingxi. CustomerID = Custid_dpcode1. CustomerID
WHERE (mingxi. Category = ' consume ') and (mingxi.[ Month] = @month)
GROUP by Custid_dpcode1. DpCode1

SELECT Custid_dpcode1. DpCode1, SUM (custid_oddfare.oddfare) as card balance total into #kye
from (SELECT custid_max_opcount). CustomerID, Mingxi_.oddfare
from (SELECT CustomerID, MAX (opcount) as Max_opcount
from #mingxi Mingxi
WHERE ([m Onth] <= @month)
GROUP by CustomerID) custid_max_opcount INNER JOIN
#mingxi mingxi_ on Custid_max_opcount. CustomerID = Mingxi_. CustomerID and
Custid_max_opcount.max_opcount = Mingxi_. Opcount)
Custid_oddfare INNER JOIN
#custid_dpcode1 custid_dpcode1 on Custid_oddfare. CustomerID = Custid_dpcode1. CustomerID
GROUP by Custid_dpcode1. DpCode1

Select @month Month, dpt.dpname1 Department, IsNull (account opening, 0) account number, IsNull (first deposit after opening, 0) the first deposit after opening, isnull (consumption amount, 0) consumption amount, isnull (consumption, 0) Consumption times, isnull (access amount, 0) Access amount, isnull (deposit and withdrawal times, 0) deposit and withdrawal times, ISNULL (total card balance, 0) Total card balance
From (SELECT DISTINCT DpCode1, DpName1
From T_department) DPT left join #kh KH on kh.dpcode1=dpt.dpcode1 left join #cq CQ on
Cq.dpcode1=dpt.dpcode1 left join #xf the XF on xf.dpcode1=dpt.dpcode1 left join #kye Kye
On Kye.dpcode1=dpt.dpcode1

drop table #mingxi
drop table #custid_dpcode1
drop table #kh
drop table #cq
drop table #xf
drop table #kye