Jeff Moden

Forum Replies Created

Viewing 15 posts - 19,291 through 19,305 (of 59,072 total)

RE: Best solution for reporting on a secondary server

I can't implement transactional replication since it works only for tables with primary keys .
That's not just a problem with transaction replication. That's one of those basic "code smells"...
--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)

November 1, 2015 at 5:47 pm

#1836956
RE: Need help to built Insert statement into Table

If you'll never have more than two levels, the following SQL will do as you ask (GREAT JOB on providing test tables and data... thanks)...
INSERT INTO dbo.myInsertedMenu
...
--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)

November 1, 2015 at 5:36 pm

#1836955
RE: Query on a large database

Sergiy (11/1/2015)
Not to mention another "little" problem with partitioning:
Msg 7736, Level 16, State 1, Line 2
Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition...
--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)

November 1, 2015 at 2:30 pm

#1836947
RE: Query on a large database

Thanks he confirmation, Sergiy,
Just to put a wrapper on this, I ran the following code to test for writes with the understanding that I've only got one disk on my...
--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)

November 1, 2015 at 1:58 pm

#1836944
RE: Query on a large database

Ok... as a bit of final proof that partitioning does very little to improve performance and can make it a bit worse, I added the following non-unique clustered index fully...
--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)

November 1, 2015 at 10:28 am

#1836940
RE: Query on a large database

Jeff Moden (11/1/2015)
@Sergiy,
An empty rowset return would certainly explain the 0 CPU time but I thought you said you loaded both tables. There are a pretty good number of...
--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)

November 1, 2015 at 9:42 am

#1836939
RE: Query on a large database

Shifting gears, I'm adding an aligned clustered index (same key as the nonpartitioned table) to the partitioned table to test the other possibility, which is also what I've done for...
--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)

November 1, 2015 at 9:25 am

#1836936
RE: Query on a large database

@Sergiy,
An empty rowset return would certainly explain the 0 CPU time but I thought you said you loaded both tables. There are a pretty good number of rows in...
--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)

November 1, 2015 at 9:23 am

#1836935
RE: How do I force SQL server to generate a new query plan ? Syntax help

Jason A. Long (10/31/2015)
Eric M Russell (10/30/2015)
In an OLTP database, you want to minimize recompiles, so you don't want to leave RECOMPILE hint in production.
I think that's an over generalized...
--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)

November 1, 2015 at 12:33 am

#1836918
RE: Query on a large database

Ah... to be sure... if partitioning could be done to separate disks, it still might not help. While I agree that bringing more spindles (and, therefor, read/write heads and...
--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 31, 2015 at 11:47 pm

#1836917
RE: Query on a large database

GilaMonster (10/31/2015)
Well, colour me (not) surprised Jeff. 😉
Looks like I might need to write a part 2 of the 'query performance tuning methodology' article....
Gosh, I'd love to be in on...
--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 31, 2015 at 11:19 pm

#1836916
RE: Are the posted questions getting worse?

TomThomson (10/31/2015)
ChrisM@Work (10/29/2015)
Ed Wagner (10/29/2015)
yb751 (10/29/2015)
I was on my last eye twitch yesterday (so to speak) so I put together this image. (attached)
I was thinking about unleashing it but then...
--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 31, 2015 at 11:03 pm

#1836913
RE: Query on a large database

Sergiy (10/31/2015)
OK, populated 2 tables from the scripts.
Here is the outcome:
set statistics time on
Select REF_AGE , COUNT(*)
from Partitiontest123.dbo.partitionedtbl
where ref_age between '27' and '30'
group by REF_AGE
set statistics time off
...
--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 31, 2015 at 10:54 pm

#1836912
RE: Query on a large database

Alright then... after getting around the double-quote problem with loading your data by changing the delimiter on the BULK INSERT from ',' to '","', I ran some performance tests using...
--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 31, 2015 at 10:39 pm

#1836910
RE: Query on a large database

I've got the data loading now.
--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 31, 2015 at 8:59 pm

#1836907

Viewing 15 posts - 19,291 through 19,305 (of 59,072 total)