| |

VerySource

 Forgot password?
 Register
Search
View: 756|Reply: 4

Why is the execution speed different?

[Copy link]

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-1-24 20:40:01
| Show all posts |Read mode
The database table StockInOutDtl has 100,000 records. It took me only 1 second to execute the following statement in the stored procedure:
      insert into @tmpStock
      SELECT B.ArtID, B.ArtDesc, B.Units, -B.Qty as Qty, 0, A.ST
      From StockInOut A Left Outer Join StockInOutDtl B
           On A.SheetID = B.SheetID
           Inner Join RefArtList C On B.ArtID = C.ArtID
      Where A.IssueDate> = @ RenewDate
                  And B.ArtID=@ArtID

But there is no "Inner Join RefArtList C On B.ArtID = C.ArtID" line, that is, the following statement takes 9 seconds:
      insert into @tmpStock
      SELECT B.ArtID, B.ArtDesc, B.Units, -B.Qty as Qty, 0, A.ST
      From StockInOut A Left Outer Join StockInOutDtl B
           On A.SheetID = B.SheetID
      Where A.IssueDate> = @ RenewDate
                  And B.ArtID=@ArtID
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-2-13 23:30:01
| Show all posts
No "Inner Join RefArtList C On B.ArtID = C.ArtID" line

More records to insert
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-2-14 06:30:01
| Show all posts
The landlord can look at the number of rows affected by the execution of two SQL statements
Reply

Use magic Report

0

Threads

66

Posts

27.00

Credits

Newbie

Rank: 1

Credits
27.00

 China

Post time: 2020-2-16 18:00:01
| Show all posts
The result product is of course large without associated conditions
Reply

Use magic Report

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

 Author| Post time: 2020-2-20 17:15:02
| Show all posts
The number of lines produced by the execution is the same!
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list