Parameter is null, return all?

Hi, heres the problem, ive got a table, and im calling a stored
procedure on the table. Now lets say a row contains the numbers 1
through 5, and i had a parameter in the sp that would be given one of
those values, and then return the corresponding row, but if they it
were passed a null value, how could i make it return all objects?Right
now i have this but i also want to return level 2, 3, 4, 5, etc:
SecurityID = ISNULL(@.SecurityLevel, '1')You can use a WHERE clause like
WHERE (
@.SecurityLevel = SecurityID
OR
@.SecurityLevel IS NULL
)
This will often perform poorly, because there is no one
query plan that can efficiently serve both cases, and another
thing to try is
WHERE (
SecurityID >= COALESCE(@.SecurityLevel,-2147483648)
AND
SecurityID <= COALESCE(@.SecurityLevel,2147483647)
)
(or with other values if the type of SecurityID is not INT).
Steve Kass
Drew University
nbs.tag@.gmail.com wrote:

>Hi, heres the problem, ive got a table, and im calling a stored
>procedure on the table. Now lets say a row contains the numbers 1
>through 5, and i had a parameter in the sp that would be given one of
>those values, and then return the corresponding row, but if they it
>were passed a null value, how could i make it return all objects?Right
>now i have this but i also want to return level 2, 3, 4, 5, etc:
>SecurityID = ISNULL(@.SecurityLevel, '1')
>
>|||SecurityID = @.SecurityLevel OR @.SecurityLevel IS NULL
Also see
http://sommarskog.se/dyn-search.html
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<nbs.tag@.gmail.com> wrote in message
news:1148911602.878618.143780@.j55g2000cwa.googlegroups.com...
> Hi, heres the problem, ive got a table, and im calling a stored
> procedure on the table. Now lets say a row contains the numbers 1
> through 5, and i had a parameter in the sp that would be given one of
> those values, and then return the corresponding row, but if they it
> were passed a null value, how could i make it return all objects?Right
> now i have this but i also want to return level 2, 3, 4, 5, etc:
> SecurityID = ISNULL(@.SecurityLevel, '1')
>

Parallel CREATE INDEX

This is apparently supported by Enterprise edition, but not by Standard
edition.
I've had a look at BOL, but am none the wiser. What does this mean exactly?
Does it mean that I can't get the standard edition to run two or more create
index commands at the same time? If so, what would happen if I attempted to
do that? Would all but the first get ignored or would SQLServer simply
queue them to run them sequentially?
Thanks
Griff
They're referring to using multiple CPU's to build the same index.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Griff" <Howling@.The.Moon> wrote in message
news:Oc49pdjfEHA.4092@.TK2MSFTNGP10.phx.gbl...
This is apparently supported by Enterprise edition, but not by Standard
edition.
I've had a look at BOL, but am none the wiser. What does this mean exactly?
Does it mean that I can't get the standard edition to run two or more create
index commands at the same time? If so, what would happen if I attempted to
do that? Would all but the first get ignored or would SQLServer simply
queue them to run them sequentially?
Thanks
Griff

Paging, Performance and ADODB

I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?
Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.
|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.

Paging at the end of a large data set

I want to do paging with my VB.NET app using SQL Server. I have a
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?
Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?
|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?
|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.
|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.
|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegro ups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks
|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegro ups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>

Paging

I've got a four way 2000 server that has a buffer cache hit ratio that is 99
or 100 % all the time. However, I am getting Pages/sec quite often on that
server between 20 and 100. Is this a problem? I'm asking because I thought
that as long as the buffer cache hit ratio was 99% or higher, all was well.
But is that necessarily true? Is there any other way that I can check to see
if this server needs more RAM?
Pages/Sec has little to do with the buffer cache hit ratio. First off those
numbers are not high at all. But you might want to see if you have other
applications than SQL Server running on the server. If so and they are run
all the time or even frequently you may want to set the max memory setting
for SQL Server to always leave memory for the OS and these other apps.
Andrew J. Kelly SQL MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:6C4D7224-22CE-451B-B5DC-B61C58780CC5@.microsoft.com...
> I've got a four way 2000 server that has a buffer cache hit ratio that is
> 99
> or 100 % all the time. However, I am getting Pages/sec quite often on
> that
> server between 20 and 100. Is this a problem? I'm asking because I
> thought
> that as long as the buffer cache hit ratio was 99% or higher, all was
> well.
> But is that necessarily true? Is there any other way that I can check to
> see
> if this server needs more RAM?
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYvMQYlBGHA.916@.TK2MSFTNGP10.phx.gbl...
> Pages/Sec has little to do with the buffer cache hit ratio. First off
> those numbers are not high at all. But you might want to see if you have
> other applications than SQL Server running on the server. If so and they
> are run all the time or even frequently you may want to set the max memory
> setting for SQL Server to always leave memory for the OS and these other
> apps.
>
Moreover a buffer cache-hit ration of 99% isn't particularly high.
Moreover, moreover an extremely high buffer cache-hit ratio often just means
you have inefficient queries which are reading and re-reading the same pages
over and over again.
David

Page Splits - What tables?

Hi everyone,
I've been looking at page splits today for the first time, after noticing
our work server's count was quite high. The number is increasing every
minute, so I thought some action needed taking.
I've done quite a bit of reading up on the subject this morning and
understand much of the theory, but I haven't found a way to track down which
of my tables are actually page splitting so regularly.
Does anyone know a method of finding this out?
Thanks in advance,
Lloyd
Use DBCC showcontig... Tables with a high % full and inserts will be
splitting... Pages with lower may have already split, or do not need
splitting... Rebuild the index to set the fill factor to allow space for
new inserts.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lloyd" <Lloyd@.discussions.microsoft.com> wrote in message
news:E3C26251-CFAA-40AD-86DF-44E9298F68D6@.microsoft.com...
> Hi everyone,
> I've been looking at page splits today for the first time, after noticing
> our work server's count was quite high. The number is increasing every
> minute, so I thought some action needed taking.
> I've done quite a bit of reading up on the subject this morning and
> understand much of the theory, but I haven't found a way to track down
> which
> of my tables are actually page splitting so regularly.
> Does anyone know a method of finding this out?
> Thanks in advance,
> Lloyd
|||Cheers Wayne
Lloyd
"Wayne Snyder" wrote:

> Use DBCC showcontig... Tables with a high % full and inserts will be
> splitting... Pages with lower may have already split, or do not need
> splitting... Rebuild the index to set the fill factor to allow space for
> new inserts.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Lloyd" <Lloyd@.discussions.microsoft.com> wrote in message
> news:E3C26251-CFAA-40AD-86DF-44E9298F68D6@.microsoft.com...
>
>
|||if a table has a clustered index on a "Monotonically Increasing" value
(identity) it will probaly not be a major culprit.
Most likely caused by tables where the clustered index is on some other
column (GUIDS for example are great fun).
If you run a showcontig as Wayne pointed out you'll see which tables are
highly fragmented (Scan Density below 80% is a sign of issues).
Fragmentation is greatly caused by Page Splits. So those are your culprits.
Note: If a table is highly fragmented, but it has less than 1,000 pages of
data, dont focus on it. Focus on the guys with thousands and thousands of
data pages.
You will see performance improve leaps and bounds when you get this fixed.
If you have questions, feel free to email me directly
cheers
Greg Jackson
PDX, Oregon