Query Tuning

I have a VB application and with the help of record sets i retrieve as many rows from the SQL Server 2000 DB.

The query that is in the SP is nested and so while retrieving it took 2 secs for a very few records and so I changed the query to a simple query but with more conditions on the WHERE clause..

But now also it takes the same 2 secs and i would like it to be reduced to milliseconds..

Any ideas are most welcome...

TIA,
NishaIf you can cut-n-paste the SQL into Query Analyzer you can Display the Query Plan (Ctrl+L) and see what Query Plan it's using, so you can determine whether indexes would help.
- Andy Abel|||It will be better if u put the script, u can use the Profiler to see the exact duration the Sql is taking,
Dont use Select * , better give the exact column names , and in the where condition u try to use id columns.
-Ashok|||Originally posted by iamnisha
I have a VB application and with the help of record sets i retrieve as many rows from the SQL Server 2000 DB.

The query that is in the SP is nested and so while retrieving it took 2 secs for a very few records and so I changed the query to a simple query but with more conditions on the WHERE clause..

But now also it takes the same 2 secs and i would like it to be reduced to milliseconds..

Any ideas are most welcome...

TIA,
Nisha

Reply-

Try to use 'primary key with clustured indexs' keys reference. after the where clause.

Query to see if an int field starts with a certain number

How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!Convert it to string, perform a substring, and then do your comparison.

Perhaps: substring(cast([thisfield] as varchar(50)),1,1)|||I don't know how the performance of this will compare, but if thisfield is non-negative, this should work as well:

[thisfield] / power(10, cast(log10([thisfield] as int))

Cheers,
-Isaac
|||

I hate to ask this, but the giant pink elephant in the room is "how do you have an int that doesn't start with a number?" What it sounds like you have is a column of string values that may or may not be an integer, and you want to see if the first character of the string is a number, right? For this it is:

thisColumn like '[1234567890]%'

But if the column is supposed to only contain integers, the best way to make sure that they are integers is to create the column using an integer datatype.

query to retrieve the columns that are null in a table

Hi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advanceHi Teixeira,
I'm not sure what you are asking. If you could supply a table creation script some test data, and what the "result" should be based on the test data, that would help enormously.

Thanks,
Cat|||USE [myDB]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[quantity1] [decimal](18, 2) NOT NULL,
[quantity2] [decimal](18, 2) NULL,
[quantity3] [decimal](18, 2) NULL,
[quantity4] [decimal](18, 2) NULL,
[quantity5] [decimal](18, 2) NULL,
[quantity6] [decimal](18, 2) NULL,
[quantity7] [decimal](18, 2) NULL,
[quantity8] [decimal](18, 2) NULL,
[quantity9] [decimal](18, 2) NULL,
[quantity10] [decimal](18, 2) NULL

this is my struture adapted.
based on this, i want to know which columns are not NULL, for my qyery result do not display for example 10 Quantity columns when i have just 3 that have quantities.|||Do you expect your query to return a single rowset, or is it possible to return multiple rows?|||yes!
It can return several rows.
but its not necessary to return columns that has null or empty values, because it would generated a lot of unnecessary columns in my datagrid display object|||I would change your structure from this:

CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[quantity1] [decimal](18, 2) NOT NULL,
[quantity2] [decimal](18, 2) NULL,
[quantity3] [decimal](18, 2) NULL,
[quantity4] [decimal](18, 2) NULL,
[quantity5] [decimal](18, 2) NULL,
[quantity6] [decimal](18, 2) NULL,
[quantity7] [decimal](18, 2) NULL,
[quantity8] [decimal](18, 2) NULL,
[quantity9] [decimal](18, 2) NULL,
[quantity10] [decimal](18, 2) NULL)

to this:

CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL)
GO
CREATE TABLE [dbo].[bookquantity](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[quantity] [decimal](18, 2) NOT NULL)
GO
ALTER TABLE [dbo].[bookquantity]
ADD CONSTRAINT FK_book (book_id) REFERENCE [books] (book_id)
GO

This way you are not tied to only 10 quantities and you can don't need to even store the NULL values.

If you can't change the structure of the table, I would suggest either creating a temp table with the above structure and populating it with the data from the master table so that you can weed out the nulls, or creating a single delimited string which the application can parse through. SQL can't really handle returning a result set with a variable number of fields.

The first suggestion would yield a result set like:
book_id quantity
------
1 12.70
1 33.45
1 9.00

The second suggestion would yield a result set like:
book_id quantity_list
--------
1 12.70|33.45|9.00

Hope this helps.
Cat|||I think you're both ideas are a good solution.
As i've some data already in the tables, normalize it more as you suggested would'd take me more time, but the second idea solves the problem perfectly.

Thanks for the help.

Teixeira

Query to Retrieve Latest Row from each group !!

Hi SQL Query Expert,
My table looks like this:
CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
1 <NULL> ABC Company 4/7/2005
11:10:10 a.m.
2 1 XYZ Company
4/8/2005 10:10:12 a.m.
3 1 AAA Company
4/8/2005 12:10:00 p.m.
4 <NULL> BBB Company 4/8/2005
1:00:00 p.m.
5 4 CCC Company
4/8/2005 2:00:00 p.m.
6 <NULL> DDD Company 4/8/2005
3:00:00 p.m.
Basically record 2 and 3 are childs of record 1. Record 5 is child of record
4. Record 6 is a parent.
Could you please give me an example on how to retrieve rows with CONTRACT_PK
equals to 3, 5 and 6 from the above table?
My goal is to retrieve the latest child row if the parent has children. If
the parent doesn't have child(s), then it retrieves parent row. Here 3 and
5
are all latest child of parent 1 and 4. Parent 6 doesn't have child, so, i
t
should get retrieved too.
The table could have 1000 rows and they all fall in the same pattern for the
records retrieval.
Thank you so much!!!
-adamTry This
Select IsNull(C.CONTRACT_PK, P.CONTRACT_PK) ContractPK,
IsNull(C.PARENT_PK, P.PARENT_PK) ParentPK,
IsNull(C.CONTRACTOR_NAME, P.CONTRACTOR_NAME) Contractor,
IsNull(C.CREATED_DATE, P.CREATED_DATE) CreatedDate
From Table P
Left Join Table C
On C.Parent_PK = P.Contract_PK
And C.Created_Date = (Select Max(Created_Date)
From Table
Where Parent_PK =
C.Parent_PK)
"adam" wrote:

> Hi SQL Query Expert,
> My table looks like this:
> CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
> 1 <NULL> ABC Company 4/7/200
5
> 11:10:10 a.m.
> 2 1 XYZ Company
> 4/8/2005 10:10:12 a.m.
> 3 1 AAA Company
> 4/8/2005 12:10:00 p.m.
> 4 <NULL> BBB Company 4/8/20
05
> 1:00:00 p.m.
> 5 4 CCC Company
> 4/8/2005 2:00:00 p.m.
> 6 <NULL> DDD Company 4/8/200
5
> 3:00:00 p.m.
> Basically record 2 and 3 are childs of record 1. Record 5 is child of reco
rd
> 4. Record 6 is a parent.
> Could you please give me an example on how to retrieve rows with CONTRACT_
PK
> equals to 3, 5 and 6 from the above table?
> My goal is to retrieve the latest child row if the parent has children. I
f
> the parent doesn't have child(s), then it retrieves parent row. Here 3 an
d 5
> are all latest child of parent 1 and 4. Parent 6 doesn't have child, so,
it
> should get retrieved too.
> The table could have 1000 rows and they all fall in the same pattern for t
he
> records retrieval.
> Thank you so much!!!
> -adam
>|||adam,
Do not post the same problem twice, it does not help. Check your first threa
d.
AMB
"adam" wrote:

> Hi SQL Query Expert,
> My table looks like this:
> CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
> 1 <NULL> ABC Company 4/7/200
5
> 11:10:10 a.m.
> 2 1 XYZ Company
> 4/8/2005 10:10:12 a.m.
> 3 1 AAA Company
> 4/8/2005 12:10:00 p.m.
> 4 <NULL> BBB Company 4/8/20
05
> 1:00:00 p.m.
> 5 4 CCC Company
> 4/8/2005 2:00:00 p.m.
> 6 <NULL> DDD Company 4/8/200
5
> 3:00:00 p.m.
> Basically record 2 and 3 are childs of record 1. Record 5 is child of reco
rd
> 4. Record 6 is a parent.
> Could you please give me an example on how to retrieve rows with CONTRACT_
PK
> equals to 3, 5 and 6 from the above table?
> My goal is to retrieve the latest child row if the parent has children. I
f
> the parent doesn't have child(s), then it retrieves parent row. Here 3 an
d 5
> are all latest child of parent 1 and 4. Parent 6 doesn't have child, so,
it
> should get retrieved too.
> The table could have 1000 rows and they all fall in the same pattern for t
he
> records retrieval.
> Thank you so much!!!
> -adam
>

Query to get rows which match with all given values

Hi all,

I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:

SELECT *
FROM TblUser u
WHERE EXISTS

(

SELECT *

FROM TblScore s

WHERE s.FKIDUser = PKIDUser

)

With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15

I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.

So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.

Someboy would have an idea at my problem ?

Thanks in advance
Jerome

Is is possible that a user may have scores that repeat? (for exmaple, 2, 2, 4, 6, 6) ?

|||

Something like this might work in 2005, but not in 2000

Code Snippet

select * from tblUser U

where PKIDUser IN

(

(

select FKIDUser from tblscore where ScoreValue = 2

intersect

select FKIDUser from tblscore where ScoreValue = 4

intersect

select FKIDUser from tblscore where ScoreValue = 6

)

except

select FKIDUser from tblScore where ScoreValue not in (2,4,6)

)

|||No, it's not possible.

And to complicate the problem, this query will be created in a stored procedure.
The specific score to search will be passed in argument to the sp.
For that, no problem, I can do it.
And I will insert these score into a temporary table (data type).

I tried to use the " = all " but I'm not sure it's the right solution.
|||

In that case, you can COUNT the records....if the total count = 3 and you've used IN (2,4,6) and these numbers cannot repeat...well, then you have your list:

Code Snippet

select * from tblUser

where PKIDUser in (

select FKIDUser

from TBLSCore

where ScoreValue in (2,4,6)

group by FKIDUser

having count(*) = 3

)

|||

Hi,

May be you can try something like this:

SELECT

U.*

FROM tblUsers As U

JOIN (

SELECT FKIDUser FROM tblScores WHERE Score = 2

UNION

SELECT FKIDUser FROM tblScores WHERE Score = 4

UNION

SELECT FKIDUser FROM tblScores WHERE Score = 6

) AS T

ON U.UserID = T.FKIDUser

Thanks & Regards,

Kiran.Y

|||

This operation is known as "relational division".

declare @.t table(scorevalue int not null unique)

insert into @.t values(2)

insert into @.t values(4)

insert into @.t values(6)

select

u.userid

from

tblusers as u

inner join

tblscore as s

on s.fkuserid = u.pkuserid

inner join

@.t t

s.scorevalue = t.scorevalue

group by

u.userid

having

count(distinct s.score) = (select count(*) from @.t)

go

I am editing my post, because I realized, while running, that you want to kick it up another notch. So, if we add the following expression to the "having"clause, then we could get the expected result.

and (select coun(distinct s2.scorevalue) from tblscore as s2 where s2.fkuserid = u.pkuserid) = (select count(*) from @.t)

Also, we can give it a try to the use of "for xml" black box, to calculate concatenate aggregation (I think that my English here is far from good).

;with agg

as

(

select

userid,

stuff(

(

select ',' + ltrim(s.scorevalue)

from tblscore as s

where s.fkuserid = u.pkuserid

order by s.scorevalue

for xml path('')

), 1, 1, '') as conc_agg

from

tbluser as u

)

select

*

from

agg

where

conc_agg = '2,4,6';

AMB

|||Hi everybody,

Thank you for your replies and sorry for my late answer but I was on vacation ^^

Finally, I used this solution:

DECLARE @.ScoreWanted TABLE (Score INT) -- 'ScoreWanted' score list

INSERT INTO @.ScoreWanted (Score) SELECT 3
INSERT INTO @.ScoreWanted (Score) SELECT 4
INSERT INTO @.ScoreWanted (Score) SELECT 6

SELECT *
FROM (
SELECT *
FROM users p
WHERE p.zone = @.p_Zone
AND p.region = @.p_Region
AND p.zipcode = @.p_ZipCode
AND p.valid = 1
AND p.called > 0
AND NOT EXISTS
(
SELECT 'x'
FROM score s
WHERE s.fk_user = p.id
AND date > @.p_PivotDate
) -- We don't keep users having a score after the pivot date
) a
WHERE a.id IN
(
SELECT s.fk_user
FROM score s
WHERE date <= @.p_PivotDate
AND s.score IN (SELECT * FROM @.ScoreWanted)
GROUP BY s.fk_user
HAVING COUNT(*) = (SELECT COUNT(*) FROM @.scorewanted)
) -- We keep only prospects having exactly the same scores that the scores coming from the

I perform a subquery for second part of my query for performance reason.
Like that I decrease the number of rows for which I need to do the second join.

Compared with my first explanation, there is here an other constraint: the pivot date for the score.
I want only users having all specified score before the pivot date and I don't want users with scores before the pivot date.
It's for that I have two separtes "where" clauses.

But I think I need to add the "distinct" word like Hunchback said.

For the xml code, Like I use Sql 2000, I think it's not supported ?

If you have anothers remarks, I'm listening you.

Thanks you.
Jerome

Query to get rows which match with all given values

Hi all,

I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:

SELECT *
FROM TblUser u
WHERE EXISTS

(

SELECT *

FROM TblScore s

WHERE s.FKIDUser = PKIDUser

)

With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15

I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.

So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.

Someboy would have an idea at my problem ?

Thanks in advance
Jerome

Is is possible that a user may have scores that repeat? (for exmaple, 2, 2, 4, 6, 6) ?

|||

Something like this might work in 2005, but not in 2000

Code Snippet

select * from tblUser U

where PKIDUser IN

(

(

select FKIDUser from tblscore where ScoreValue = 2

intersect

select FKIDUser from tblscore where ScoreValue = 4

intersect

select FKIDUser from tblscore where ScoreValue = 6

)

except

select FKIDUser from tblScore where ScoreValue not in (2,4,6)

)

|||No, it's not possible.

And to complicate the problem, this query will be created in a stored procedure.
The specific score to search will be passed in argument to the sp.
For that, no problem, I can do it.
And I will insert these score into a temporary table (data type).

I tried to use the " = all " but I'm not sure it's the right solution.
|||

In that case, you can COUNT the records....if the total count = 3 and you've used IN (2,4,6) and these numbers cannot repeat...well, then you have your list:

Code Snippet

select * from tblUser

where PKIDUser in (

select FKIDUser

from TBLSCore

where ScoreValue in (2,4,6)

group by FKIDUser

having count(*) = 3

)

|||

Hi,

May be you can try something like this:

SELECT

U.*

FROM tblUsers As U

JOIN (

SELECT FKIDUser FROM tblScores WHERE Score = 2

UNION

SELECT FKIDUser FROM tblScores WHERE Score = 4

UNION

SELECT FKIDUser FROM tblScores WHERE Score = 6

) AS T

ON U.UserID = T.FKIDUser

Thanks & Regards,

Kiran.Y

|||

This operation is known as "relational division".

declare @.t table(scorevalue int not null unique)

insert into @.t values(2)

insert into @.t values(4)

insert into @.t values(6)

select

u.userid

from

tblusers as u

inner join

tblscore as s

on s.fkuserid = u.pkuserid

inner join

@.t t

s.scorevalue = t.scorevalue

group by

u.userid

having

count(distinct s.score) = (select count(*) from @.t)

go

I am editing my post, because I realized, while running, that you want to kick it up another notch. So, if we add the following expression to the "having"clause, then we could get the expected result.

and (select coun(distinct s2.scorevalue) from tblscore as s2 where s2.fkuserid = u.pkuserid) = (select count(*) from @.t)

Also, we can give it a try to the use of "for xml" black box, to calculate concatenate aggregation (I think that my English here is far from good).

;with agg

as

(

select

userid,

stuff(

(

select ',' + ltrim(s.scorevalue)

from tblscore as s

where s.fkuserid = u.pkuserid

order by s.scorevalue

for xml path('')

), 1, 1, '') as conc_agg

from

tbluser as u

)

select

*

from

agg

where

conc_agg = '2,4,6';

AMB

|||Hi everybody,

Thank you for your replies and sorry for my late answer but I was on vacation ^^

Finally, I used this solution:

DECLARE @.ScoreWanted TABLE (Score INT) -- 'ScoreWanted' score list

INSERT INTO @.ScoreWanted (Score) SELECT 3
INSERT INTO @.ScoreWanted (Score) SELECT 4
INSERT INTO @.ScoreWanted (Score) SELECT 6

SELECT *
FROM (
SELECT *
FROM users p
WHERE p.zone = @.p_Zone
AND p.region = @.p_Region
AND p.zipcode = @.p_ZipCode
AND p.valid = 1
AND p.called > 0
AND NOT EXISTS
(
SELECT 'x'
FROM score s
WHERE s.fk_user = p.id
AND date > @.p_PivotDate
) -- We don't keep users having a score after the pivot date
) a
WHERE a.id IN
(
SELECT s.fk_user
FROM score s
WHERE date <= @.p_PivotDate
AND s.score IN (SELECT * FROM @.ScoreWanted)
GROUP BY s.fk_user
HAVING COUNT(*) = (SELECT COUNT(*) FROM @.scorewanted)
) -- We keep only prospects having exactly the same scores that the scores coming from the

I perform a subquery for second part of my query for performance reason.
Like that I decrease the number of rows for which I need to do the second join.

Compared with my first explanation, there is here an other constraint: the pivot date for the score.
I want only users having all specified score before the pivot date and I don't want users with scores before the pivot date.
It's for that I have two separtes "where" clauses.

But I think I need to add the "distinct" word like Hunchback said.

For the xml code, Like I use Sql 2000, I think it's not supported ?

If you have anothers remarks, I'm listening you.

Thanks you.
Jerome

query to detemine trigger status

I know how to retrieve the list of triggers in teh database.
Select * from sysobjects where xtype = 'TR'
What I want to know is how do i tell whether the trigger is disabled or
enabled. I would appreciate if anybody can help me this.
thanks
MB...and status&2048=2048
will tell you if it's disabled
Mohan wrote:

>I know how to retrieve the list of triggers in teh database.
>Select * from sysobjects where xtype = 'TR'
>What I want to know is how do i tell whether the trigger is disabled or
>enabled. I would appreciate if anybody can help me this.
>thanks
>MB
>
>|||Lookup the argument ExecIsTriggerDisabled for the meta-data function
OBJECTPROPERTY in SQL Server Books Online.
Anith

Query to ADD/SELECT values from an SQL

Hello.

I need a query that will RETRIEVE a value from a database if it is present, but if the data isn't present, then the data will be INSERTed into the table.

Either way, I need the row returned at the end of the query.

I can do SELECT queries, but I don't have a clue as to how to proceed with branching statements.

For example:

User runs a query for "Canada".
Canada exists in the database, so the database returns Canada along with its ID.

Next user runs a query for "Chile".
Chile isn't in the database so a record is created and the ID (an IDENTITY field) is returned.

Does anyone know how I may accomplish this?something like this:

create proc MyProc
(
 @.countryid int out,
 @.countryname nvarchar(50)
)
set @.countryid = -1
select @.countryid = CountryId
from MyTable
where CountryName = @.countryname
if (@.countryid = -1)
begin
 insert into MyTable (CountryName)
 values (@.countryname)
 select @.countryid = scope_identity()
end

|||or

IF NOT EXISTS ( Select countryId from from MyTable where CountryName = @.countryname)
 INSERT INTO MyTable (CountryName) values (@.countryname)

hth