Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,461 through 41,475 (of 59,102 total)

RE: optimize queries

You shouldn't have to partition anything for a 7k row table because it won't help when returning all rows and columns.
Something is dreadfully wrong if it takes that long to...
--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)

October 20, 2009 at 5:37 pm

#1068609
RE: Need help with a query to concatenate records

A "search" on this site is still one of the best ways to find what you need...
http://www.sqlservercentral.com/search/?cx=000517265726492607871%3Agqdiynsp-y0&cof=FORID%3A9&q=concatenate&sa=Go#1257
When you run across one of the many articles or scripts on this subject,...
--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)

October 20, 2009 at 5:32 pm

#1068605
RE: extact last 3 characters from a string

That will certainly work and if you don't have many rows, you probably won't notice, but REVERSE is comparatively very expensive... it would be computationally cheaper if you just used...
--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)

October 20, 2009 at 5:51 am

#1068104
RE: Concatenating Rows

No problem... just remember that it leaves a trailing comma and does the whole table unless you add a criteria.
--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)

October 20, 2009 at 5:44 am

#1068101
RE: Get index scan on zip code radius query - not sure why.. ! ?

matt6749 (10/19/2009)
Ok thanks Jeff - I am going to try, and try hard, to implement multiple CTEs like you suggest.. (gulp, wish me luck!) - Matt
You'll do fine... just...
--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)

October 20, 2009 at 5:40 am

#1068100
RE: Get index scan on zip code radius query - not sure why.. ! ?

On second thought, since you need the calculated distance as part of the result set, you may want to include the calculated distance in the first CTE which is limited...
--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)

October 19, 2009 at 11:25 pm

#1067962
RE: Get index scan on zip code radius query - not sure why.. ! ?

Actually, it's probably not...
In a CTE, create a result set using the "Narrow Horizontal Corridor" formulas we talked about. Then, in another CTE, do the radius calculations on the...
--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)

October 19, 2009 at 11:19 pm

#1067958
RE: Get index scan on zip code radius query - not sure why.. ! ?

To be clear, you'll still need to do the radius calculation but the addition of the MAX variables I spoke of should really cut down on the number of complex...
--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)

October 19, 2009 at 11:12 pm

#1067952
RE: Get index scan on zip code radius query - not sure why.. ! ?

matt6749 (10/19/2009)
Wow, Jeff, you are the man! I will try to implement the "narrow corridor".. so i should just calculate the Max vars and add "WHERE Lat2 BETWEEN...
--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)

October 19, 2009 at 11:04 pm

#1067951
RE: Compare yesterday's data to today's

Shairal-763732 (10/19/2009)
Newbie question...
Our home office overwrites a table every night with new data. I want to know when a new row appears in the table, but if they...
--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)

October 19, 2009 at 10:50 pm

#1067949
RE: Selecting data from multiple rows into a single row

mspiegel (10/19/2009)
Bru,
I'm not familiar with the Pivot syntax. Could you provide a sample for me?
Thanks!
Yep... same priciple as in the following...
http://www.sqlservercentral.com/articles/T-SQL/63681/
--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)

October 19, 2009 at 10:46 pm

#1067947
RE: Querying rows by Date with StartDate and EndDate fields (aka What would Jeff Moden do?)

Heh... don't forget the false physicians. 😛
--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)

October 19, 2009 at 10:42 pm

#1067944
RE: Good testing practices in SQL server

Heh... at least you're doing SOME testing. A lot of shops that I've done work for generally let the users find the problems for them... we all know how...
--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)

October 19, 2009 at 10:14 pm

#1067940
RE: Get index scan on zip code radius query - not sure why.. ! ?

The biggest reason why you're getting a SCAN of any sort instead of a SEEK is because of the forumula itself... it must calculate every zip code to find which...
--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)

October 19, 2009 at 10:00 pm

#1067937
RE: Are the posted questions getting worse?

WayneS (10/19/2009)
Hey Jeff... can you FedEx me your biggest, baddest pork chop launcher? I think I'm in dire need of it.....
Just had a "developer" send me some code.
SP "a" has...
--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)

October 19, 2009 at 8:50 pm

#1067924

Viewing 15 posts - 41,461 through 41,475 (of 59,102 total)