Viewing 15 posts - 5,806 through 5,820 (of 7,613 total)
The second style would at least have a chance of being fully optimized, particularly if you force a recompile of the query. Stick with it.
I'd avoid the first style...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2014 at 11:01 am
The problem with a table is that one option must apply to everyone. Parameter or other methods allow customized runs, so that your own special cases can still run...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 15, 2014 at 4:10 pm
Eirikur Eiriksson (9/15/2014)
ScottPletcher (9/15/2014)
Two quick possibilities:
1) Add an optional parameter to the proc which requires a certain value or the proc just...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 15, 2014 at 3:01 pm
That GROUP BY is gonna kill your performance. See if the code below is close to what you need.
I've added clustered indexes on the temp tables prior to loading...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 15, 2014 at 2:56 pm
You can't consider missing index info in isolation. At the very least, you also need to look at index usage stats. Both of those are the minimum needed...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 15, 2014 at 12:27 pm
The big delay is likely in allocating and formatting log space, not in the identity number assignment, which is trivial.
Thus, before you run the big INSERT, pre-allocate sufficient log space...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 15, 2014 at 12:23 pm
All of those seem far too disruptive to me.
Two quick possibilities:
1) Add an optional parameter to the proc which requires a certain value or the proc just exits (or does...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 15, 2014 at 12:09 pm
Luis Cazares (9/11/2014)
Please find the errors in the following code:
INSERT INTO dbo.FactInternetSalesCopy
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 11, 2014 at 4:29 pm
thomashohner (9/9/2014)
So it skips...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 9, 2014 at 3:14 pm
Most likely is that a database backup is in progress, and you can't alter the database files during a backup. Check running processes for a backup, dbcc check, or...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 9, 2014 at 11:00 am
EXEC ('DELETE FROM onedb.stg.tab1') AT [linkserv]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 8, 2014 at 3:26 pm
You'll also need to decide if you're willing to allow the rounding to change the day/date or not. That is, if the time is, say, "20140908 23:59:59.670", do you...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 8, 2014 at 3:23 pm
The first, most obvious though is "don't do this". Why are you denormalizing one table's data into thousands of other tables? Why not just join to the original...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 8, 2014 at 3:18 pm
terry999 (9/5/2014)
Nice one..Why date_input + '0'
I think it works without concatenating '0' at the end?
That's in case you get an entry like:
'Saturday'
Without the " + '0'", the entire string gets...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 5, 2014 at 9:40 am
You could skip all non-numeric chars at the front of the string, viz:
SELECT
date_input,
SUBSTRING(date_input, PATINDEX('%[0-9]%', date_input + '0'), 30) AS adjusted_date_input
FROM (
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 5, 2014 at 9:12 am
Viewing 15 posts - 5,806 through 5,820 (of 7,613 total)