WayneS

Forum Replies Created

Viewing 15 posts - 1,186 through 1,200 (of 5,588 total)

RE: Can this be converted to a set based query?

Tony,
Here's a new TVF to try out. It is no longer an inline TVF; it's now a multi-statement TVF. I'm trying to hit some of the larger tables only once,...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 7, 2011 at 2:18 pm

#1335311
RE: Can this be converted to a set based query?

Tony,
I'm trying to redo the iTVF, and I have a few questions:
1. For any Invoice.InvoiceId value, will there be one and only one row in InvoiceTotal for that InvoiceId?
2. For...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 7, 2011 at 12:50 pm

#1335257
RE: Get one row from query

... or this (it will only hit the #AllLocations table once, not twice. However, it will scan the entire table. Previous query will take advantage of indexes if available on...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 7, 2011 at 12:22 pm

#1335237
RE: Are the posted questions getting worse?

GilaMonster (6/7/2011)
The client-from-hell is no more.
Celebration time!
....... or did you take that sword thingy of yours in to work and do away with them all?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 7, 2011 at 12:14 pm

#1335230
RE: Can this be converted to a set based query?

cgrammont (6/7/2011)
I have really nothing of significance to add. But I'd like to say "thank you" for going through this problem for everyone to see. The thought process, going through...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 7, 2011 at 8:37 am

#1335055
RE: Simple Recovery Model

To answer your question... just clustering.
The other available options:
1. Shipping the transaction log: requires Bulk Logged for Full recovery models.
2. Mirroring: requires Full recovery model.
It's a lot easier to manage...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 9:26 pm

#1334771
RE: GO

Whoops... quoted my post instead of editing it. It's now corrected...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 9:21 pm

#1334770
RE: GO

Actually, none of those choices are correct. You get back 50 random GUID values.
1. NEWID() returns a random GUID (aka uniqueidentifier)
2. Select statement has no order by clause, so the...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 9:16 pm

#1334769
RE: Can this be converted to a set based query?

Are you talking about the TotalGross,TotalNet, TotalUnits?
If so, try this instead:
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @Col2Use sysname,
@SQLCmd ...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 9:11 pm

#1334768
RE: Are the posted questions getting worse?

Jim,
If I've read things right, you're going to the Summit? Do you know that Thursdays up there is "Kilt Day"? Grant started it... he can fill you in on it!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 9:00 pm

#1334767
RE: Can this be converted to a set based query?

Tony,
Okay, I don't think any additional indexes will help out... this looks to be about as efficient as possible as it is currently written.
I do see that the branch that...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 3:31 pm

#1334701
RE: Can this be converted to a set based query?

FYI: Wrong procedure. Need the one that has the parametrized string, calling sp_executesql at the end.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 3:14 pm

#1334687
RE: Can this be converted to a set based query?

tfeuz (6/6/2011)
Wayne,
I am certainly not going to change taking your advice at this point 😉
That also explains why my plan was show operations on the invoice table of over 20%
But...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 2:40 pm

#1334670
RE: Can this be converted to a set based query?

Tony,
Can you add SET STATISTICS IO,TIME ON; to the test that you're running (not to the procedures), and send the results?
Please do it for "slow" and "InvoiceKeyFirst" methods.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 11:42 am

#1334572
RE: Can this be converted to a set based query?

tfeuz (6/6/2011)
Wayne,
Just so we are on the same page -- this is the current SP we are working with. The ones using string concatentation took at least 2-3...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

June 6, 2011 at 11:31 am

#1334560

Viewing 15 posts - 1,186 through 1,200 (of 5,588 total)