Viewing 15 posts - 4,726 through 4,740 (of 7,619 total)
For one thing, "save to table" is a lot of overhead. Save it to file(s) instead, then load the file(s) into tables later to analyze.
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".
October 13, 2015 at 10:14 am
For absolute max speed, you should also cluster the temp table on ID:
SELECT TOP (0) IDENTITY(int, 1, 1) AS ID, ClustKey1, ClustKey2 --, ...
INTO #TEMP
FROM F_POLICY_TRANSACTION
CREATE CLUSTERED INDEX TEMP__CL ON...
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".
October 13, 2015 at 10:02 am
When you create the temp table, i.e before it's loaded, cluster it on ( COUNT, MESS ). A nonclustered index will not be efficient. That will speed up...
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".
October 12, 2015 at 3:22 pm
So is the clustered index on the table permanent, and you just add nonclustered indexes? The clustered index is the most critical index. The best clustered index keys...
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".
October 12, 2015 at 3:17 pm
I don't have time to line up all the columns, but here's the general format of what you need. I don't see where the computed @FT* values are being...
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".
October 12, 2015 at 3:02 pm
Eric M Russell (10/12/2015)
Kristen-173977 (10/9/2015)
Eric M Russell (10/9/2015)
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".
October 12, 2015 at 11:54 am
Even more important would be properly (uniquely) clustering the tblRecipeAllergen table on ( Recipe_number, Allergen ) (and removing AllergenId from the table, as it's not 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".
October 9, 2015 at 1:24 pm
You're welcome!
Ooh, one last interesting thing. If both the "local" table and the "outer" table have a column with that name, SQL will use the "local" ("closer") table. ...
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".
October 9, 2015 at 12:01 pm
A subquery can refer to a column in the outer/main query. Thus, SQL is comparing the column to itself. You need to prefix the column with the local...
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".
October 9, 2015 at 10:42 am
Since all of table "a" would seem to fit in a single page, flip the table order in the CROSS JOIN, i.e. FROM b CJ a rather than FROM a...
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".
October 9, 2015 at 10:40 am
ChrisM@Work (10/8/2015)
Greg Edwards-268690 (10/8/2015)
Aggregates must first gather all results.
I am not at all surprised it is generally faster.
Many times I...
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".
October 8, 2015 at 11:21 am
Another possibility to consider:
SELECT CustomerNumber,
CAST(CAST(DATEDIFF(DAY, MIN(SalesDate), MAX(SalesDate)) AS decimal(9, 2)) / (COUNT(*) - 1) / 30.0 AS decimal(9, 2)) AS Avg_Months_Btwn_Sales
FROM dbo.your_tablename
GROUP BY CustomerNumber
--ORDER BY...
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".
October 8, 2015 at 9:41 am
drew.allen (10/7/2015)
ScottPletcher (10/7/2015)
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".
October 7, 2015 at 2:49 pm
Michael L John (10/7/2015)
Nope, I actually double checked
Interesting. That's just really not logical to me. The NOT EXISTS() will have to do a full scan of the table...
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".
October 7, 2015 at 2:09 pm
Michael L John (10/7/2015)
ScottPletcher (10/7/2015)
Michael L John (10/7/2015)
ScottPletcher (10/7/2015)
SELECT ra.Recipe_number, r.Recipe_name
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".
October 7, 2015 at 1:55 pm
Viewing 15 posts - 4,726 through 4,740 (of 7,619 total)