Viewing 15 posts - 5,701 through 5,715 (of 7,619 total)
Try fso. It's reasonably fast if you don't go too crazy on the number of files. I left out the error checking, you can fill that in if...
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 17, 2014 at 10:32 am
Since the table has 1.5B rows, the ~860K rows accessed is only ~0.06%, so I can see why SQL chose to do (a lot of) SEEKs rather than a scan....
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 16, 2014 at 3:25 pm
Probably the easiest way to do that is to create views which limit the data by date or whatever, then give them access to only the views. But that...
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 16, 2014 at 2:35 pm
inevercheckthis2002 (10/16/2014)
So I take the same code and try to create a stored procedure by adding this:
IF OBJECT_ID('usp_DataLoadTime', 'P') IS NOT NULL
DROP PROC usp_DataLoadTime
GO
CREATE PROC usp_DataLoadTime
AS
BEGIN
... <code 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 16, 2014 at 2:32 pm
The single biggest factor for performance is to get the best clustered index on every table.
Then adjust the non-clustered indexes as required.
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 16, 2014 at 2:14 pm
Alexander Suprun (10/16/2014)
ScottPletcher (10/16/2014)
Alexander Suprun (10/16/2014)
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 16, 2014 at 2:12 pm
Alexander Suprun (10/16/2014)
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 16, 2014 at 1:51 pm
From a purely performance stand-point, we'd almost certainly want to process Countries prior to it being JOINed:
SELECT A.City_Name, B.Country_Code, B.Country_Area
FROM Cities A
INNER JOIN (
SELECT Country_Code,...
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 16, 2014 at 1:36 pm
The GO separates the code into "batches". SQL parses and prepares an execution plan separately for each batch.
Thus, without the GO, when SQL parses the query below...
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 16, 2014 at 1:28 pm
kd11 (10/16/2014)
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 16, 2014 at 1:17 pm
I'd think you'd definitely want to use LTRIM() and RTRIM() to make sure you get rid of extraneous spaces. I added ISNULL(NULLIF(...)...) just in case there isn't a dash...
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 16, 2014 at 12:55 pm
If you can live with only a three-char month, you can remove the STUFF() function. If you need to have the full month name, you'll need the STUFF() part...
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 16, 2014 at 12:34 pm
I admit, I name all constraints myself, including DEFAULTs, even though for DEFAULTs that may be deprecated.
CREATE TABLE Theory (
Id int identity(1,1) CONSTRAINT Theory__PK primary key,
Data varchar(20),
LogDate datetime CONSTRAINT Theory__DF_LogDate...
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 16, 2014 at 12:21 pm
Do you have some type of id or other unique/key value on the row?
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 15, 2014 at 12:56 pm
I agree with others, particularly on ISNULL(). In fact, there's a simple, very easy to remember rule:
NEVER use ISNULL() in a WHERE or JOIN clause.
If, as is typical, the...
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 15, 2014 at 11:11 am
Viewing 15 posts - 5,701 through 5,715 (of 7,619 total)