Forum Replies Created
Viewing 15 posts - 57,466 through 57,480 (of 59,067 total)
-
RE: What makes it faster?
Ugh! Correlated Sub-Queries = Instant RBAR. Not good... real performance killer. Should be a derived table. WHERE IN... not good... should be an equi-join (inner join).
Haven't tested it but I'm...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: How to debug stored procedure in Express version
I don't use debuggers in SQL Server... just the ol' Mil Spec Mark I Mod I Eyeball. Probably not what you wanted to hear, though...
What are you trying to debug...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Beware of Search Argument (SARG) Data Types
I loved the examples... nice and simple. Great job, DC.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Day of Week Function
Yep... this is about 3 times faster (I tested both)...
CREATE FUNCTION dbo.GetDOWCount
(
@StartDate DATETIME,
@EndDate DATETIME,
@DOW VARCHAR(9) --Monday, Tuesday, Wednesday, etc
)
RETURNS INT
AS
BEGIN
RETURN (SELECT...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Split a field into multiple records
Actually, a function would slow stuff down here... you want the full table to be split... why do it a line at a time? Try this, instead...
--==================================================================================================
-- This...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Sql server 2000 Locking
For what? Inserts, Updates, Deletes, or Selects?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Problems with CASE statement
Try something like this, instead...
SELECT
locationId, locationId AS original_locationId, locationCode, description, divisionCode, companyId
FROM locations
ORDER BY companyId,
CASE
WHEN locationCode NOT LIKE '%[^0-9]%' THEN STR(locationcode,10)
ELSE locationcode
END,
locationCode
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Problems with CASE statement
ISNUMERIC does NOT mean IS ALL DIGITS... you're making a BIG mistake using it for that. If you don't think so, try this...
SELECT NULL AS [ASCII#],'12D45' AS [Character(s)],ISNUMERIC('12D45')...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Char limit to create a view
Heh, heh, heh.... leave it to Serqiy to just say it like it is
Splitting the tables like this is, in fact,...--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Char limit to create a view
Fabiano,
Ok... that's about 40 characters per select including the UNION ALL... that's only 100 tables if you use VARCHAR(4000) or NVARCHAR(4000). So, use VARCHAR(8000) and just use EXEC... don't use...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Datetime Issue
Just a note folks... anytime you use a column name in a formula in the WHERE or ON clause of a SELECT, you make it impossible for the coveted INDEX...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: dropping gloabl temporary table
Serqiy beat me to it! STOP USING SYSTEM TABLES FOR SUCH SIMPLE CHECKS!!! Use the system functions like Serqiy did.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: TYPE CAST ERROR
Oh... no, no, no, no... ISNUMERIC does not adequately test for IS A NUMBER or IS ALL DIGITS...
Try this...
SELECT ISNUMERIC('$1,000')
SELECT CAST('$1,000' AS MONEY)
SELECT CAST('$1,000' AS INT)
GO
SELECT ISNUMERIC('0d05')
SELECT...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: Char limit to create a view
Fabiano,
What is the pattern of you table names? And are they in the same database or different databases? I have the same problem with some 3rd party software at work...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
RE: aggregate functions using time intervals
You did all the hard work... I think this is probably what you're looking for... the changes will group your output by date and hour...
- select
- DATEADD(hh,DATEDIFF(hh,0,Time_Perf),0) AS Date_Time,avg(total_processor) as 'total...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 57,466 through 57,480 (of 59,067 total)