WayneS

Forum Replies Created

Viewing 15 posts - 1,216 through 1,230 (of 5,588 total)

RE: Save ALL plans in 1 operation

Does this work?
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 3, 2011 at 11:13 am

#1333829
RE: Are the posted questions getting worse?

Garadin (6/3/2011)
Heh. I'm surprised at 2 things.
1. They all fit on one line (On my screen anyways).
2. I actually know what they all are!
Very nice list though.
Is...
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 3, 2011 at 11:02 am

#1333821
RE: Are the posted questions getting worse?

Stefan Krzywicki (6/3/2011)
GilaMonster (6/3/2011)
Garadin (6/3/2011)
I saw the big bowl of alphabet soup get added onto Gus' signature and figured this would be the place to figure out what led up...
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 3, 2011 at 10:58 am

#1333817
RE: Save ALL plans in 1 operation

I'll try it out on my 2005 vm tonight. However, I've never had problems saving - it's always saved all plans.
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 3, 2011 at 10:55 am

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

Ninja's_RGR'us (6/3/2011)
WayneS (6/3/2011)
Mr. Ninja... I know you're lurking... do you see any other things to do, or that you would do differently?
Nothing major... at this point I'd also be whacking...
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 3, 2011 at 10:42 am

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

Mr. Ninja... I know you're lurking... do you see any other things to do, or that you would do differently?
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 3, 2011 at 10:26 am

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

WayneS (6/3/2011)
On dbo.InvoiceTotalAdditionalCosts, do you have any indexes? It would benefit from:
CREATE INDEX IX_1 ON dbo.InvoiceTotalAdditionalCosts (InvoiceKey, ProductRebateCategory) INCLUDE(Amount, Type)
Don't add this one... add those INCLUDE columns to the existing...
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 3, 2011 at 10:26 am

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

tfeuz (6/3/2011)
Well that go it down to about 18 seconds....
Is it possible that what I am trying to do can not be optimized down to just a few seconds and...
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 3, 2011 at 10:20 am

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

On dbo.InvoiceTotalAdditionalCosts, do you have any indexes? It would benefit from:
CREATE INDEX IX_1 ON dbo.InvoiceTotalAdditionalCosts (InvoiceKey, ProductRebateCategory) INCLUDE(Amount, Type)
What is the definition of the _dta_index_Invoice_24_1044198770__K9_K1_K2_K92_K42_K17_3_5_11_18_19_23_43_44_117 index on the dbo.Invoice table?
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 3, 2011 at 10:04 am

#1333780
RE: Save ALL plans in 1 operation

Right click the graphical plan, select "Save Execution Plan As..."
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 3, 2011 at 9:41 am

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

tfeuz (6/3/2011)
I made all the changes requested...
Altered the index order and included the column
This got rid of the table scan.
Changed the data types from 18,6 to 18,0
This got rid of...
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 3, 2011 at 9:32 am

#1333765
RE: Need Help on T-Sql Tricky Query.

The IN operator needs distinct values, ie. IN (1,2,3,4) or IN ('A','B','C','D').
When they are combined in one variable, it is not expanded out.
You'll need to either split them out, or...
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 3, 2011 at 9:08 am

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

tfeuz (6/3/2011)
Working through all the suggested changes....
but the function has this syntax error:
Msg 102, Level 15, State 1, Procedure fnCalculateRuleInvoiceUnits_Single, Line 46
Incorrect syntax near ','.
I cannot really see the issue?
Whoops,...
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 3, 2011 at 8:57 am

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

In both procedures, and the function, change the @RuleId parameter from decimal(18,6) to decimal(18,0) to match the actual datatype of the columns.
Edit: In the RB_InvoicesToProcess and the RB_InvoicesToProcess_Moved tables, 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 3, 2011 at 8:45 am

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

Ninja's_RGR'us (6/3/2011)
WayneS (6/3/2011)
tfeuz (6/3/2011)
Odd---
I dropped the redundant index
Added the new index to the rb_invoicestoprocess table (invoicekey,ruleid)
Made the change to the SP as you suggested....
It actually went from 23 seconds up...
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 3, 2011 at 8:36 am

#1333727

Viewing 15 posts - 1,216 through 1,230 (of 5,588 total)