query assistance: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

Dear All,
I have a single table name "Remark". It contains the item code, date,
status and remark. If I want to build a query that select items out which
the latest status is still in "pending" for example. How do I make it?
Thanks
Best Rdgs
EllisEllis
SELECT <columns list>
FROM Remarks WHERE[date]=(SELECT TOP 1 [date]
FROM Remarks R WHERE
R.Itemcode=Remarks.Itemcode
ORDER BY [date] DESC)
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:uSSJ$UXbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I have a single table name "Remark". It contains the item code,
date,
> status and remark. If I want to build a query that select items out which
> the latest status is still in "pending" for example. How do I make it?
> Thanks
> Best Rdgs
> Ellis
>sql

Query help

For reasons i wont go into i need to have an identity field when returning a
select statement
ie Select * from Table1 returnn
Field1 field2 etc
aa aa aa
bb bb bb
and i want
Field1 field2 etc IDENT
aa aa aa 0
bb bb bb 1
any clues ?Assuming that you PK is field1 and the order of Id cols is also based on Fie
ld1
select
t1.*,
(select count(*) from table1 t2 where t2.Field1 < t1.Field1) as IdCol
from Table1 t1
- Sha Anand
"Peter Newman" wrote:

> For reasons i wont go into i need to have an identity field when returning
a
> select statement
> ie Select * from Table1 returnn
> Field1 field2 etc
> aa aa aa
> bb bb bb
> and i want
> Field1 field2 etc IDENT
> aa aa aa 0
> bb bb bb 1
> any clues ?|||How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Peter Newman" wrote:

> For reasons i wont go into i need to have an identity field when returning
a
> select statement
> ie Select * from Table1 returnn
> Field1 field2 etc
> aa aa aa
> bb bb bb
> and i want
> Field1 field2 etc IDENT
> aa aa aa 0
> bb bb bb 1
> any clues ?

Wednesday, March 28, 2012

query help

Query Help

Hoping the community can help me with this problem. I have a stored
procedure that returns a few alias fields.
Here is the select clause to give you an idea what I am talking about (FROM
and WHERE omitted to shorten the length):
SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
Table].LocationName,
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
END AS DLPhoto,
CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
[DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current Driver
Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
[DriverEmploymentDateHired] >= '8/17/2001' AND
[DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
This query has 4 alias's to be returned; DLRenewDate,DLPhoto,[DPre-Employ],
and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
not want the record to be returned with the query. If I could have a WHERE
like this:
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULL
then it would not be a problem, but you cannot test alias's in this manner
(as far as I can tell). What are my options for solving this problem? If I
was in Access I could run a second query on the first and test my
conditions. Can I use another Stored procedure in this manner?
For Example a second stored procedure that works like this:
Select * From StoredProcedure1
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULLHi
To reduce the amount of typing you have to do, you can use a table alias for
the table. see Books Online:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_05_4ofn.htm
Instead of using the column alias in the WHERE clause, use the actual
expression. As these are CASE statements returning non null values when they
are satisfied they will only return null if non of the clauses are returned,
so these can be tested in the WHERE clause
e.g
For:
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days'
END AS DLRenewDate
This will only be null if
NOT ( DriverLicenseRenewDate <= GETDATE()
OR ( DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE()) ) )
Which is the same as
DriverLicenseRenewDate > GETDATE()
AND DriverLicenseRenewDate >= DATEADD(dd, 60, GETDATE()) )
HTH
John
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>|||That makes sense, thank you.
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>

Query Help

How can I select the count of the distinct values of a
column '
Thanks.Try:
select
count (distinct MyCol)
from
MyTable
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
How can I select the count of the distinct values of a
column '
Thanks.|||SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
FROM YourTable
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.|||Thanks but I forgot to add that it is a char column with
the numbers in it '

>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>
>.
>|||It makes no difference. You wanted a count of the distinct values. From
Northwind, try:
select
count (distinct CustomerID)
from
Orders
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:503401c4c5c7$6ed7d9a0$a301280a@.phx.gbl...
Thanks but I forgot to add that it is a char column with
the numbers in it '

>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>
>.
>|||Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>|||Thanx much. It sure means a lot. I saw Itzik and his wife in April.
They're both doing well. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OBcRNPtyEHA.3820@.TK2MSFTNGP11.phx.gbl...
Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>

Query Help

Hi i need some help in a Query,
i have a table
Table1 ( field1) with values 1,2,3,4 in the field 1.
when i run,
select field1 from table1
the query returns
1
2
3
4
but instead i need to get these values comma seperated
like 1,2,3,4. i dont want to use cursors for this.
can somebody help me in this.
thanks in advance.
sathyaHi
The only safe way is to use a cursor, alternatively the better method it to
do it on the client or a third party tool such as http://rac4sql.net/
John
"sathya" <softwaremaniac@.hotmail.com> wrote in message
news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> Hi i need some help in a Query,
> i have a table
> Table1 ( field1) with values 1,2,3,4 in the field 1.
> when i run,
> select field1 from table1
> the query returns
> 1
> 2
> 3
> 4
> but instead i need to get these values comma seperated
> like 1,2,3,4. i dont want to use cursors for this.
> can somebody help me in this.
> thanks in advance.
> sathya|||Isn't this something cool:
declare @.List varchar(1000)
select @.List = coalesce(@.List + ',', '') + ltrim(rtrim(col1)) from table1
select @.List
hth
Quentin
"sathya" <softwaremaniac@.hotmail.com> wrote in message
news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> Hi i need some help in a Query,
> i have a table
> Table1 ( field1) with values 1,2,3,4 in the field 1.
> when i run,
> select field1 from table1
> the query returns
> 1
> 2
> 3
> 4
> but instead i need to get these values comma seperated
> like 1,2,3,4. i dont want to use cursors for this.
> can somebody help me in this.
> thanks in advance.
> sathya|||Hi
This solution has been suggested by many people in the past (including
myself) but it is not a safe solution.
John
"Quentin Ran" <ab@.who.com> wrote in message
news:%23AGtH7CjDHA.360@.TK2MSFTNGP10.phx.gbl...
> Isn't this something cool:
> declare @.List varchar(1000)
> select @.List = coalesce(@.List + ',', '') + ltrim(rtrim(col1)) from table1
> select @.List
> hth
> Quentin
>
> "sathya" <softwaremaniac@.hotmail.com> wrote in message
> news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> > Hi i need some help in a Query,
> >
> > i have a table
> >
> > Table1 ( field1) with values 1,2,3,4 in the field 1.
> >
> > when i run,
> >
> > select field1 from table1
> > the query returns
> > 1
> > 2
> > 3
> > 4
> >
> > but instead i need to get these values comma seperated
> > like 1,2,3,4. i dont want to use cursors for this.
> >
> > can somebody help me in this.
> > thanks in advance.
> >
> > sathya
>|||hi john,
What is the problem you are expecting
for me is in a loop, if i use it will slow down the
process.
it works fine for me
thanks
>--Original Message--
>Hi
>This solution has been suggested by many people in the
past (including
>myself) but it is not a safe solution.
>John
>"Quentin Ran" <ab@.who.com> wrote in message
>news:%23AGtH7CjDHA.360@.TK2MSFTNGP10.phx.gbl...
>> Isn't this something cool:
>> declare @.List varchar(1000)
>> select @.List = coalesce(@.List + ',', '') + ltrim(rtrim
(col1)) from table1
>> select @.List
>> hth
>> Quentin
>>
>> "sathya" <softwaremaniac@.hotmail.com> wrote in message
>> news:060901c38bfc$65979c00$a001280a@.phx.gbl...
>> > Hi i need some help in a Query,
>> >
>> > i have a table
>> >
>> > Table1 ( field1) with values 1,2,3,4 in the field 1.
>> >
>> > when i run,
>> >
>> > select field1 from table1
>> > the query returns
>> > 1
>> > 2
>> > 3
>> > 4
>> >
>> > but instead i need to get these values comma
seperated
>> > like 1,2,3,4. i dont want to use cursors for this.
>> >
>> > can somebody help me in this.
>> > thanks in advance.
>> >
>> > sathya
>>
>
>.
>

Query Help

Hi there,
I need some help with a query that is giving duplicate results. The
query looks like this:
SELECT products.idProduct, products.sku, products.description,
products.price, products.highPrice, products.listhidden,
products.listprice, products.serviceSpec, products.bToBPrice,
products.smallImageUrl, products.noprices, products.sDesc,
products.stock, products.noStock, products.pcprod_HideBTOPrice,
products.FormQuantity, products.pcProd_BackOrder, products.IDBrand,
Brands.BrandName, products.showInHome FROM products JOIN
categories_products ON
products.idProduct=categories_products.idProduct LEFT OUTER JOIN
Brands ON products.IDBrand=Brands.IDBrand WHERE active=-1 AND
configOnly=0 and removed=0 AND (products.idProduct = 6936 OR
products.idProduct = 6800 OR products.idProduct = 6935 OR
products.idProduct = 8531 OR products.idProduct = 8778 OR
products.idProduct = 16881 OR products.idProduct = 16057 OR
products.idProduct = 16587 OR products.idProduct = 16880 OR
products.idProduct = 18160) ORDER BY products.showInHome ASC,
products.visits DESC, products.sales DESC
The problem is that a product can appear in more than 1 category so
the same idProduct may appear more than once in the
categories_products table. I need to use that join in the statement,
however, as sometimes I add this to the query to further filter
products:
"AND categories_products.idCategory = 160"
I've tried to do a GROUP BY but with no success, what I really want to
do is make this query only pull distinct idProduct but obviously I
cannot just add "SELECT DISTINCT" when there is more than 1 field in
the query. Any ideas?
-DrewIf I understand what you want, you could just add a DISTINCT to the select.
However, the way I would do it is to remove the join on categories_products
and rewrite the query as (note that I also changed that long list of OR's to
an IN). Using the list of OR's will work, but the IN is simpler and easier
to understand (at least to me).
SELECT products.idProduct,
products.sku,
products.description,
products.price,
products.highPrice,
products.listhidden,
products.listprice,
products.serviceSpec,
products.bToBPrice,
products.smallImageUrl,
products.noprices,
products.sDesc,
products.stock,
products.noStock,
products.pcprod_HideBTOPrice,
products.FormQuantity,
products.pcProd_BackOrder,
products.IDBrand,
Brands.BrandName,
products.showInHome
FROM products
LEFT OUTER JOIN Brands ON products.IDBrand=Brands.IDBrand
WHERE active=-1
AND configOnly=0
and removed=0
AND products.idProduct IN (6936, 6800, 6935, 8531, 8778, 16881, 16057,
16587, 16880, 18160)
ORDER BY products.showInHome ASC, products.visits DESC, products.sales DESC
Then if you need to add the condition that the idProduct is found in the
categories_products table with a categories_products.idCategory = 160, just
add the following to the WHERE clause:
AND EXISTS (SELECT * FROM categories_products
WHERE products.idProduct=categories_products.idProduct
AND categories_products.idCategory = 160)
Tom
"Drubage" <drew@.tribalectic.com> wrote in message
news:fa067b56-5b9d-400d-b111-5c10057cb2b6@.i29g2000prf.googlegroups.com...
> Hi there,
> I need some help with a query that is giving duplicate results. The
> query looks like this:
> SELECT products.idProduct, products.sku, products.description,
> products.price, products.highPrice, products.listhidden,
> products.listprice, products.serviceSpec, products.bToBPrice,
> products.smallImageUrl, products.noprices, products.sDesc,
> products.stock, products.noStock, products.pcprod_HideBTOPrice,
> products.FormQuantity, products.pcProd_BackOrder, products.IDBrand,
> Brands.BrandName, products.showInHome FROM products JOIN
> categories_products ON
> products.idProduct=categories_products.idProduct LEFT OUTER JOIN
> Brands ON products.IDBrand=Brands.IDBrand WHERE active=-1 AND
> configOnly=0 and removed=0 AND (products.idProduct = 6936 OR
> products.idProduct = 6800 OR products.idProduct = 6935 OR
> products.idProduct = 8531 OR products.idProduct = 8778 OR
> products.idProduct = 16881 OR products.idProduct = 16057 OR
> products.idProduct = 16587 OR products.idProduct = 16880 OR
> products.idProduct = 18160) ORDER BY products.showInHome ASC,
> products.visits DESC, products.sales DESC
> The problem is that a product can appear in more than 1 category so
> the same idProduct may appear more than once in the
> categories_products table. I need to use that join in the statement,
> however, as sometimes I add this to the query to further filter
> products:
> "AND categories_products.idCategory = 160"
> I've tried to do a GROUP BY but with no success, what I really want to
> do is make this query only pull distinct idProduct but obviously I
> cannot just add "SELECT DISTINCT" when there is more than 1 field in
> the query. Any ideas?
> -Drew|||On Mar 31, 5:59=A0pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:
> If I understand what you want, you could just add a DISTINCT to the select=.
> However, the way I would do it is to remove the join on categories_product=s
> and rewrite the query as (note that I also changed that long list of OR's =to
> an IN). =A0Using the list of OR's will work, but the IN is simpler and eas=ier
> to understand (at least to me).
> SELECT products.idProduct,
> =A0products.sku,
> =A0products.description,
> =A0products.price,
> =A0products.highPrice,
> =A0products.listhidden,
> =A0products.listprice,
> =A0products.serviceSpec,
> =A0products.bToBPrice,
> =A0products.smallImageUrl,
> =A0products.noprices,
> =A0products.sDesc,
> =A0products.stock,
> =A0products.noStock,
> =A0products.pcprod_HideBTOPrice,
> =A0products.FormQuantity,
> =A0products.pcProd_BackOrder,
> =A0products.IDBrand,
> =A0Brands.BrandName,
> =A0products.showInHome
> FROM products
> LEFT OUTER JOIN Brands ON products.IDBrand=3DBrands.IDBrand
> WHERE active=3D-1
> =A0 AND configOnly=3D0
> =A0 and removed=3D0
> =A0 AND products.idProduct IN (6936, 6800, 6935, 8531, 8778, 16881, 16057,=
> =A0 =A0 16587, 16880, 18160)
> ORDER BY products.showInHome ASC, products.visits DESC, products.sales DES=C
> Then if you need to add the condition that the idProduct is found in the
> categories_products table with a categories_products.idCategory =3D 160, j=ust
> add the following to the WHERE clause:
> AND EXISTS (SELECT * FROM =A0categories_products
> =A0 =A0 =A0WHERE products.idProduct=3Dcategories_products.idProduct
> =A0 =A0 =A0 =A0 =A0AND categories_products.idCategory =3D 160)
> Tom
> "Drubage" <d...@.tribalectic.com> wrote in message
> news:fa067b56-5b9d-400d-b111-5c10057cb2b6@.i29g2000prf.googlegroups.com...
>
> > Hi there,
> > I need some help with a query that is giving duplicate results. The
> > query looks like this:
> > SELECT products.idProduct, products.sku, products.description,
> > products.price, products.highPrice, products.listhidden,
> > products.listprice, products.serviceSpec, products.bToBPrice,
> > products.smallImageUrl, products.noprices, products.sDesc,
> > products.stock, products.noStock, products.pcprod_HideBTOPrice,
> > products.FormQuantity, products.pcProd_BackOrder, products.IDBrand,
> > Brands.BrandName, products.showInHome FROM products JOIN
> > categories_products ON
> > products.idProduct=3Dcategories_products.idProduct LEFT OUTER JOIN
> > Brands ON products.IDBrand=3DBrands.IDBrand WHERE active=3D-1 AND
> > configOnly=3D0 and removed=3D0 AND (products.idProduct =3D 6936 OR
> > products.idProduct =3D 6800 OR products.idProduct =3D 6935 OR
> > products.idProduct =3D 8531 OR products.idProduct =3D 8778 OR
> > products.idProduct =3D 16881 OR products.idProduct =3D 16057 OR
> > products.idProduct =3D 16587 OR products.idProduct =3D 16880 OR
> > products.idProduct =3D 18160) ORDER BY products.showInHome ASC,
> > products.visits DESC, products.sales DESC
> > The problem is that a product can appear in more than 1 category so
> > the same idProduct may appear more than once in the
> > categories_products table. I need to use that join in the statement,
> > however, as sometimes I add this to the query to further filter
> > products:
> > "AND categories_products.idCategory =3D 160"
> > I've tried to do a GROUP BY but with no success, what I really want to
> > do is make this query only pull distinct idProduct but obviously I
> > cannot just add "SELECT DISTINCT" when there is more than 1 field in
> > the query. Any ideas?
> > =A0 -Drew- Hide quoted text -
> - Show quoted text -
Worked like a charm, thank you so much!
-Drew

query help

I want to find days when people either haven't entered at least 8 hours of
time or days where they haven't entered time at all.
If I select out of the table that holds the time I can get days where they
haven't entered at least 8 hours (i.e. select emp_hours from emp_time where
emp_hours < 8). But how do I also get days where time hasn't been entered
since those days won't be in the table?
Thanks,
--
Dan D.Create a calendar table and do an outer join against that table. Some info on calendar tables:
http://www.aspfaq.com/show.asp?id=2519
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BED0D9F5-2515-4A14-B902-B2334EF59A57@.microsoft.com...
>I want to find days when people either haven't entered at least 8 hours of
> time or days where they haven't entered time at all.
> If I select out of the table that holds the time I can get days where they
> haven't entered at least 8 hours (i.e. select emp_hours from emp_time where
> emp_hours < 8). But how do I also get days where time hasn't been entered
> since those days won't be in the table?
> Thanks,
> --
> Dan D.|||Is there any other way? I'd rather not have to maintain another table.
Thanks,
--
Dan D.
"Tibor Karaszi" wrote:
> Create a calendar table and do an outer join against that table. Some info on calendar tables:
> http://www.aspfaq.com/show.asp?id=2519
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BED0D9F5-2515-4A14-B902-B2334EF59A57@.microsoft.com...
> >I want to find days when people either haven't entered at least 8 hours of
> > time or days where they haven't entered time at all.
> >
> > If I select out of the table that holds the time I can get days where they
> > haven't entered at least 8 hours (i.e. select emp_hours from emp_time where
> > emp_hours < 8). But how do I also get days where time hasn't been entered
> > since those days won't be in the table?
> >
> > Thanks,
> > --
> > Dan D.
>|||You can create that table on the fly, as a temp table. But I fail to see that problem of having such
table. You create it once and for all. If you hold 10 years, it is only about 3650 rows in it!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BA3C552B-FA37-4657-B77A-1074C512E9AD@.microsoft.com...
> Is there any other way? I'd rather not have to maintain another table.
> Thanks,
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
>> Create a calendar table and do an outer join against that table. Some info on calendar tables:
>> http://www.aspfaq.com/show.asp?id=2519
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:BED0D9F5-2515-4A14-B902-B2334EF59A57@.microsoft.com...
>> >I want to find days when people either haven't entered at least 8 hours of
>> > time or days where they haven't entered time at all.
>> >
>> > If I select out of the table that holds the time I can get days where they
>> > haven't entered at least 8 hours (i.e. select emp_hours from emp_time where
>> > emp_hours < 8). But how do I also get days where time hasn't been entered
>> > since those days won't be in the table?
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>

Query Help

How can I select the count of the distinct values of a
column '
Thanks.Try:
select
count (distinct MyCol)
from
MyTable
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
How can I select the count of the distinct values of a
column '
Thanks.|||SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
FROM YourTable
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.|||Thanks but I forgot to add that it is a char column with
the numbers in it '
>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>> How can I select the count of the distinct values of a
>> column '
>> Thanks.
>
>.
>|||Please ignore the message about the column being a char
column........
Thanks.
>--Original Message--
>How can I select the count of the distinct values of a
>column '
>Thanks.
>.
>|||It makes no difference. You wanted a count of the distinct values. From
Northwind, try:
select
count (distinct CustomerID)
from
Orders
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:503401c4c5c7$6ed7d9a0$a301280a@.phx.gbl...
Thanks but I forgot to add that it is a char column with
the numbers in it '
>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>> How can I select the count of the distinct values of a
>> column '
>> Thanks.
>
>.
>|||Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>|||Thanx much. It sure means a lot. I saw Itzik and his wife in April.
They're both doing well. :-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OBcRNPtyEHA.3820@.TK2MSFTNGP11.phx.gbl...
Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>

Query Help

Hi All,
I'm not sure if this even possible.
Here is the query from NorthWind database:
SELECT TOP 3 Employees.EmployeeID,
Orders.CustomerID
FROM Orders,Employees
WHERE Employees.EmployeeID = 9
AND Orders.EmployeeID = Employees.EmployeeID
Result:
EmployeeID CustomerID
9 RICSU
9 ERNSH
9 SAVEA
Is there a way to return this query as 1 record and name columns
CustomerID1, CustomerID2, CustomerID3
EmployeeID CustomerID1 CustomerID2
CustomerID3
9 RICSU ERNSH
SAVEA
Thanks,
WalterI'd say do that in your user interface code rather than in SQL. Leave
the database for what it does best - retrieving data. How difficult can
it be to display a data set as horizontal list?
Certainly you can do it in the database, but do you really want 3
*random* customers for each employee? You haven't specified ORDER BY in
your query so you can't predict which three customers you'll get back.
Let's assume that you really wanted the first three orders placed by an
employee:
SELECT MAX(employeeid),
MAX(CASE WHEN ord = 1 THEN customerid END),
MAX(CASE WHEN ord = 2 THEN customerid END),
MAX(CASE WHEN ord = 3 THEN customerid END)
FROM
(SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
FROM Orders AS O1
JOIN Orders AS O2
ON O1.employeeid = O2.employeeid
AND (O1.orderdate > O2.orderdate OR
(O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
WHERE O1.employeeid =9
GROUP BY O1.employeeid, O1.customerid, O1.orderdate
HAVING COUNT(*)<=3) AS T ;
--
David Portas
SQL Server MVP
--|||David,
Thanks for your help.Works like a champ!
I don't have an interface for this query.
Walter
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124208616.358495.95330@.g47g2000cwa.googlegroups.com...
> I'd say do that in your user interface code rather than in SQL. Leave
> the database for what it does best - retrieving data. How difficult can
> it be to display a data set as horizontal list?
> Certainly you can do it in the database, but do you really want 3
> *random* customers for each employee? You haven't specified ORDER BY in
> your query so you can't predict which three customers you'll get back.
> Let's assume that you really wanted the first three orders placed by an
> employee:
> SELECT MAX(employeeid),
> MAX(CASE WHEN ord = 1 THEN customerid END),
> MAX(CASE WHEN ord = 2 THEN customerid END),
> MAX(CASE WHEN ord = 3 THEN customerid END)
> FROM
> (SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
> FROM Orders AS O1
> JOIN Orders AS O2
> ON O1.employeeid = O2.employeeid
> AND (O1.orderdate > O2.orderdate OR
> (O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
> WHERE O1.employeeid =9
> GROUP BY O1.employeeid, O1.customerid, O1.orderdate
> HAVING COUNT(*)<=3) AS T ;
> --
> David Portas
> SQL Server MVP
> --
>

Query Help

Hoping the community can help me with this problem. I have a stored
procedure that returns a few alias fields.
Here is the select clause to give you an idea what I am talking about (FROM
and WHERE omitted to shorten the length):
SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
Table].LocationName,
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
END AS DLPhoto,
CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
[DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current Driver
Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
[DriverEmploymentDateHired] >= '8/17/2001' AND
[DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
This query has 4 alias's to be returned; DLRenewDate,DLPhoto,[DPre-Employ],
and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
not want the record to be returned with the query. If I could have a WHERE
like this:
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULL
then it would not be a problem, but you cannot test alias's in this manner
(as far as I can tell). What are my options for solving this problem? If I
was in Access I could run a second query on the first and test my
conditions. Can I use another Stored procedure in this manner?
For Example a second stored procedure that works like this:
Select * From StoredProcedure1
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULLHi
To reduce the amount of typing you have to do, you can use a table alias for
the table. see Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_05_4ofn.htm
Instead of using the column alias in the WHERE clause, use the actual
expression. As these are CASE statements returning non null values when they
are satisfied they will only return null if non of the clauses are returned,
so these can be tested in the WHERE clause
e.g
For:
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days'
END AS DLRenewDate
This will only be null if
NOT ( DriverLicenseRenewDate <= GETDATE()
OR ( DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE()) ) )
Which is the same as
DriverLicenseRenewDate > GETDATE()
AND DriverLicenseRenewDate >= DATEADD(dd, 60, GETDATE()) )
HTH
John
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>|||That makes sense, thank you.
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>sql

Monday, March 26, 2012

Query goes from 1 second to 40 minutes with only 1 more record?

I have a very strange problem where I have a simple select count(*) with a handle of of joined tables that works fine and returns a count of records in under 1 second when the criteria in the where clause limits the count to 36984. But if I change the criteria to where the number of records would be 1 higher, 36985, the query takes ~40 minutes!

Here's the query

SELECT count(*)
FROM AT JOIN Al ON Al.AlID= AT.ALID
JOIN DProfile ON Al.DProfileID= DProfile.DProfileID
JOIN Label ON DProfile.LabelID = Label.LabelID
JOIN PricingTier ON PricingTier.LabelID = DProfile.LabelID
JOIN PricingTarget on PricingTarget.PricingTargetID = PricingTier.PricingTargetID
JOIN ATP ON ATP.PricingTierID = PricingTier.PricingTierID
WHERE PricingTarget.Target = 'AT'
AND PricingTier.MaxAgeInDays = 0
AND ATP.LengthMultiple = 0
AND AT.ATID > 408095
AND AT.ATID < 451199

Notice the part of the where clause in red. This is how I'm changing the number of rows that select count(*) should find.

Here's the output from showplan

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[AL].[ALID])=([MGN2].[dbo].[AT].[ALID]))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[PricingTier].[PricingTargetID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[ATP].[PricingTierID]))
| | | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATP].[PK_ATPID]), WHERE:([MGN2].[dbo].[ATP].[LengthMultiple]=(0)))
| | | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), SEEK:([MGN2].[dbo].[PricingTier].[PricingTierID]=[MGN2].[dbo].[ATP].[PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), SEEK:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408095) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

Now, if I change the part of the where clause from 'AND AT.ATID > 408095' to 'AND AT.ATID > 408094', increase the number returned by one, the query goes from taking 1 second to 40 minutes. Here's the showplan text that is different, when the only difference in the query is changing the number in the where clause.

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Merge Join(Inner Join, MERGE:([MGN2].[dbo].[PricingTarget].[PricingTargetID])=([MGN2].[dbo].[PricingTier].[PricingTargetID]), RESIDUAL:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]))
|--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[AL].[ALID]=[MGN2].[dbo].[AT].[ALID]))
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[ATPrice].[PricingTierID]=[MGN2].[dbo].[PricingTier].[PricingTierID]))
| |--Sort(ORDER BY:([MGN2].[dbo].[PricingTier].[PricingTargetID] ASC))
| | |--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| | |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATPrice].[PK_ATPriceID]), WHERE:([MGN2].[dbo].[ATPrice].[LengthMultiple]=(0)))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408094) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

I tried increasing the size of the TempDB from the default of 8 MB to 1000 MB for both the files, but it behaves the exact same way. I'm at a loss as to what is causing this dramatic difference for such a simple query. Anyone have an idea?

Thanks!

By the way, this is on SQL Server 2005, if that helps any.|||

Jim:

Good post. I appreciate your inclusion of the plan and the research you put into your question. It appears to me that you are at a plan "crossover" point. What I mean is that you are at the point where the mere addition of one more record to the output range causes the query optimizer to choose a different query plan. In your plan I see many "clustered index scans". A cover index might be appropriate to improve the performance of your query. Can you post the indexes, keys and unique constraints of all of your tables?

What follows below is academic. I am not sure whether I need to include this or not because it looks like you have already done this with your testing, so it looks like you already have a good handle on this. If this is of no interest, just skip the rest.

Dave

I created a mock-up table with 32767 rows of data. One of the columns is a "testDate" column with an associated index. After creating the table I performed a number of select queries with SHOWPLAN_TEXT turned on so that I could discover the filter criteria at which the query plan switched from "INDEX SEEK" to "CLUSTERED INDEX SCAN". I found that when I switch the filter date from "1/31/7" to "1/30/7" that the plan switched. Therefore I ran these two queries with SHOWPLAN _TEXT turned on to illustrate the query plan "crossover" point. I think that your query has a similar issue. My main question to you is was it your intent to find the crossover or is this something that jumped up and bit you? OUCH!

if exists
( select 0 from sysobjects
where type = 'U'
and id = object_id ('dbo.crossoverTest')
)
drop table dbo.crossoverTest
go
create table dbo.crossoverTest
( rid integer
constraint pk_crossoverTest primary key,
filler char (200),
testDate datetime
)
go
create index testDate_ndx
on dbo.crossoverTest (testDate)
go
insert into dbo.crossoverTest
select iter,
'Filler',
dateadd (mi, -17*iter, convert(datetime, '2/3/7'))
from (
select 256*b.number + a.number as iter
from master.dbo.spt_values a (nolock)
inner join master.dbo.spt_values b (nolock)
on a.[name] is null
and b.[name] is null
and b.number <= 127
and a.number <= 255
and 256*b.number + a.number > 0
) as small_iterator
go
update statistics dbo.crossoverTest
go
set showplan_text on
go
select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/31/7'
go
-- StmtText
-- --
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[crossoverTest]) WITH PREFETCH)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[crossoverTest].[testDate_ndx]), SEEK:([crossoverTest].[testDate] >= 'Jan 31 2007 12:00AM') ORDERED FORWARD)
-- Table 'crossoverTest'. Scan count 1, logical reads 813, physical reads 0, read-ahead reads 0.
go
select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/30/7'
go
-- StmtText
--
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[crossoverTest].[pk_crossoverTest]), WHERE:([crossoverTest].[testDate]>='Jan 30 2007 12:00AM'))
-- Table 'crossoverTest'. Scan count 1, logical reads 913, physical reads 0, read-ahead reads 0.

go
set showplan_text off
go

|||

Thanks Dave,

Ultimately, a simple index was the issue. What perplexed me was just that the difference between the queries when it returned one more row was soooo dramatic. And to answer your question, I had just come upon the crossover issue through trial and error. I hadnt noticed the query plans were different until I got to the point that 1 row was the difference between success and failure.

Query From Excel

SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
When I run this query, I get this response:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'Sheet1$'.
The table either does not exist or the current user does not have permission
s
on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$'].
Any idea why?David
The error message is pretty clear. Do you have a [Sheet1$]) ? Do you have
permissions to access the file?
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message
news:9FF48606-8332-461E-9F23-662633D592F2@.microsoft.com...
> SELECT * FROM
> OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\test.xls', [Sheet1$])
> When I run this query, I get this response:
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table
> 'Sheet1$'.
> The table either does not exist or the current user does not have
> permissions
> on that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$'].
> Any idea why?|||Hi CaptainSlock,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you are executing the OPENROWSET but
failed to get the error.
I create a new Excel xls file and input some data on Sheet1, executing the
same query from Query Analyzer, I could get the correct result set as
expected. So would you please help me check the following settings?
1. Please confirm your Excel file contain a Sheet named "Sheet1" (Defautly,
a new Excel will have this sheet)
2. Please confirm you are using local SQL Server instance or the excel file
is located on the local disk
3. What account you are using have sufficient permission on this Excel file.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1.
I am logged in with an ID that has Admin access to the PC that I am running
directly from. The sql server is on a separate box - Windows Server 2003,
SQL Server 2000 Enterprise.
"Michael Cheng [MSFT]" wrote:

> Hi CaptainSlock,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you are executing the OPENROWSET but
> failed to get the error.
> I create a new Excel xls file and input some data on Sheet1, executing the
> same query from Query Analyzer, I could get the correct result set as
> expected. So would you please help me check the following settings?
> 1. Please confirm your Excel file contain a Sheet named "Sheet1" (Defautly
,
> a new Excel will have this sheet)
> 2. Please confirm you are using local SQL Server instance or the excel fil
e
> is located on the local disk
> 3. What account you are using have sufficient permission on this Excel fil
e.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1. I a
m
logged in with an ID that has Admin access to the PC that I am running
directly from. The sql server is on a separate box - Windows Server 2003,
SQL Server 2000 Enterprise.
"Uri Dimant" wrote:

> David
> The error message is pretty clear. Do you have a [Sheet1$]) ? Do you have
> permissions to access the file?
>
> "David Samson" <CaptainSlock@.nospam.nospam> wrote in message
> news:9FF48606-8332-461E-9F23-662633D592F2@.microsoft.com...
>
>|||Well, try the following query
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...Sheet1$
Also take a look at Q306397 HOWTO: Use Excel w/ SQL Linked Servers &
Distributed Queries
http://support.microsoft.com/suppor...s/q306/3/97.asp
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message
news:1212A046-58F7-4B00-8EFE-1984FD9F4814@.microsoft.com...
>I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1. I
>am
> logged in with an ID that has Admin access to the PC that I am running
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Uri Dimant" wrote:
>|||Hi
You could try opening the file in DTS to see what sheet names you have, this
will also prove you have the correct access permissions or alternatively
create a new file and paste the data into that then there definately be a
sheet1.
Martin
"David Samson" wrote:
> Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet
1.
> I am logged in with an ID that has Admin access to the PC that I am runnin
g
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Michael Cheng [MSFT]" wrote:
>|||Dear all,
I've trying a very similar query and I obtain the following error from the
Sql Server 2005 CTP(June):
Msg 15501, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of
component 'Ad Hoc Distributed Queries' because this component is turned off
as part of the security configuration for this server. A system administrato
r
can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. Fo
r
more information about enabling 'Ad Hoc Distributed Queries', see "Surface
Area Configuration" in SQL Server Books Online.
From my sql server 2000 sp3a:
Servidor: mensaje 7314, nivel 16, estado 1, l_nea 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table
'Clusterdb$'. The table either does not exist or the current user does not
have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Clusterdb$'].
Clusterdb exists as a sheet
"David Samson" wrote:
> Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet
1.
> I am logged in with an ID that has Admin access to the PC that I am runnin
g
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Michael Cheng [MSFT]" wrote:
>|||I found the problem. I was assuming that the reference to 'C:\test.xls' was
local. It's not, it's actually from the reference point of the server
itself. It makes sense, now that I think about it. So, once I copied the
file over to the C:\ of the database server, it worked like a champ. This i
s
the query I used:
select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
"John Bell" wrote:
> Hi
> You could try opening the file in DTS to see what sheet names you have, th
is
> will also prove you have the correct access permissions or alternatively
> create a new file and paste the data into that then there definately be a
> sheet1.
> Martin
> "David Samson" wrote:
>|||I found the problem. I was assuming that the reference to 'C:\test.xls' was
local. It's not, it's actually from the reference point of the server
itself. It makes sense, now that I think about it. So, once I copied the
test.xls file over to the C:\ of the database server, it worked like a champ
.
This is the query I used:
select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
"Uri Dimant" wrote:

> Well, try the following query
> SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...Sheet1$
>
> Also take a look at Q306397 HOWTO: Use Excel w/ SQL Linked Servers &
> Distributed Queries
> http://support.microsoft.com/suppor...s/q306/3/97.asp
>
> "David Samson" <CaptainSlock@.nospam.nospam> wrote in message
> news:1212A046-58F7-4B00-8EFE-1984FD9F4814@.microsoft.com...
>
>

query from different sql servers

how can i get data from different sqlservers in tsql.
is there anyway different from below
Select * from opendatasource('SQLOLEDB','Data Source=...;User
ID=...;Password=...').dbname.dbo.tblnameSabri,
From the SQL BOL:
This example accesses data from a table on another instance of SQL Server.
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
HTHJerry"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:29DFD275-8D2B-438C-8BFF-9C9B743EFC76@.microsoft.com...
> how can i get data from different sqlservers in tsql.
> is there anyway different from below
> Select * from opendatasource('SQLOLEDB','Data Source=...;User
> ID=...;Password=...').dbname.dbo.tblname
>

Friday, March 23, 2012

Query for multiple tables

Hi,

I have a query that returns values from three seperate data tables into a dataset:

SELECT [MediaFileData].[FileIdentifier], [AudioPCData].[RecorderID],
[AudioPCData].[Channel], [MediaFileData].[SubmittingUser], [MediaFileData].[DateTimeAsString],
[MetaData].[FieldText]

FROM [MediaFileData] INNER JOIN [AudioPCData] ON MediaFileData.FileIdentifier = AudioPCData.FileIdentifier
INNER JOIN [MetaData] ON MediaFileData.FileIdentifier = MetaData.FileIdentifier
INNER JOIN [SSRData] ON MediaFileData.FileIdentifier = SSRData.FileIdentifier

WHERE ([MediaFileData].[SubmittingUser] = '{0}') AND ([AudioPCData].[RecorderID] = '{0}')
AND ([MediaFileData].[MediaDescription] = '{0}') AND ([SSRData].[ModelUsed] = '{0}')

Each table has only got one 'FileIdentifier' apart from MetaData. This table has three columns 'FileIdentifer', 'FiledName' and 'FieldText'. One file can have more than one field and therefore It will have the same 'FileIdentifier' e.g.

FileIdentifier FieldName FieldText

1 Field 1 Hello

1 Field 2 Goodbye

The first problem is I only want to display the first and second field 'FiledText' in my results grid but still load all the other fields into my dataset.

The second problem is that it creates a new row for every field, whereas I want the fields with the same 'FileIdentifier' to be in the same row!

At the moment mt results gridlooks like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello Hello

1 MyPC 1 Me 03/05/07 14:24 GoodBye Goodbye

I need it to look like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello GoodBye

Thanks,

Guy

Depends on which version of SQL Server you are using. Using SQL 2k5 you should take a look on Cross apply.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Guy,

Could you be more clear as to what your goal is, and what the purpose of these fields might be, their scope, the number of overlapping entries, and generally how you view this thing to be structured? On first blush, this seems like a situation where a relational database setup could save you some storage and access time in the long run if you can identify the shared fields and refactor your tables. The problem with setting Field1 and Field2 appropriately is also interesting in that you want Field1 to have "hello" in it and field2 to have "goodbye" in it whereas other than that string nothing differs between the records. This behaviour seems highly specific to your problem, and so knowing more about what your goals are for this database might help us to identify how best to solve your immediate problems in such a manner as to have long-term benefits.

Some natural things to weigh might be:

How do these components logically relate, and what are valid values for things in the database? (e.g. identical entries with field1 and field2 values, should this happen?, should field1 and field2 be mirrored?, etc.).

Does this solution need to last, or is it a one-time only operation?

How important is it to retain the database structure?

Is there a rule to how you want this join to work in the long run that requires domain specific knowledge?

How important is the performance of your database in the long run?

How often do you intend to perform this operation, and how much development work is "worth it"?

I think that as we understand the constraints of your task and what you intend to accomplish, we can find a solution that best fits your needs.

Thanks,

John (MSFT)

Wednesday, March 21, 2012

Query execution time problem

We are executing following query from query analyzer,
SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
[Table Strcuture => id int, data varchar(500)]
This table contains 45000 records.
When this query is executed from Query Analyzer locally on the database
server, output is returned in 30 seconds.
However, when the query is executed from Query Analyzer installed on
application server it takes 110 seconds.
Execution plan is same both times. CPU, reads are identical for both
executions. However there is big difference between the execution times.
Also, both database server and application server in same VLAN and direct
network connectivity is available without any interim hops.
Any kind of help or suggestion giving insight into this aspect is welcomed.
Thanks in advance for all the inputs.pvv30 (pvv30@.discussions.microsoft.com) writes:
> We are executing following query from query analyzer,
> SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
> [Table Strcuture => id int, data varchar(500)]
> This table contains 45000 records.
> When this query is executed from Query Analyzer locally on the database
> server, output is returned in 30 seconds.
> However, when the query is executed from Query Analyzer installed on
> application server it takes 110 seconds.
> Execution plan is same both times. CPU, reads are identical for both
> executions. However there is big difference between the execution times.
> Also, both database server and application server in same VLAN and direct
> network connectivity is available without any interim hops.
The most obvious guess is that network latency matters. It may be a good
VLAN, but on the database server the connection is over shared memory
so it's not unreasonable that it takes more time to get it over the
wire.
Another thing to consider is the CPU power on the two machines.
Particularly if you run the output in text mode in QA, this could
matter.
Then again, I don't really see the point in measuring how long time
takes to receive 45000 rows in Query Analyzer. The tool is not really
intended for that.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Query Analyzer was used just to indicate a client application.
We are also monitoring the SQL calls using Profiler which is also giving us
the same output.
Also, the two boxes are of same configuration in terms of RAM & CPU.
You mentioned about connection being created in shared memory. Can anyone
please explain it in more detail as to how will it impact the query
performance? It might help us in understanding the problem better and work
towards a solution.
This time we enabled client statistics option in Query Analyzer. The
difference between two queries is for following attributes,
1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
(app server)] v/s 81 [for 30 sec execution (locally from database server)]
2. "Number of bytes received" recieved is slightly more for query executed
from app server compared to database server
What does "Cumulative wait time on server replies" stats represents and how
to interpret it?
Thanks in advance for the responses.
"Erland Sommarskog" wrote:

> pvv30 (pvv30@.discussions.microsoft.com) writes:
> The most obvious guess is that network latency matters. It may be a good
> VLAN, but on the database server the connection is over shared memory
> so it's not unreasonable that it takes more time to get it over the
> wire.
> Another thing to consider is the CPU power on the two machines.
> Particularly if you run the output in text mode in QA, this could
> matter.
> Then again, I don't really see the point in measuring how long time
> takes to receive 45000 rows in Query Analyzer. The tool is not really
> intended for that.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||pvv30 (pvv30@.discussions.microsoft.com) writes:
> Query Analyzer was used just to indicate a client application.
I don't think it's a good benchmark for that purpose, since QA does a lot
of things a client app wouldn't do. You are better of writing a client
application that just receives the dataset. Particularly, you should use
the client API you plan to use in your client application.

> You mentioned about connection being created in shared memory. Can anyone
> please explain it in more detail as to how will it impact the query
> performance? It might help us in understanding the problem better and work
> towards a solution.
Normally clients communicate with SQL Server over network connection, most
commonly these days is TCP/IP, but there is also support for named pipes
and a few more protocols.
But when the client and server are on the same physical machine, using
the network is bit of overkill and therefore there is the possibiliy to
use shared memory, and this is also the default when connecting locally.
You can use the Client Network Utility to turn off shared memory, but
even with TCP/IP locally, you would see much better execution times
locally than over the network.

> This time we enabled client statistics option in Query Analyzer. The
> difference between two queries is for following attributes,
> 1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
> (app server)] v/s 81 [for 30 sec execution (locally from database server)]
> 2. "Number of bytes received" recieved is slightly more for query executed
> from app server compared to database server
> What does "Cumulative wait time on server replies" stats represents and
> how to interpret it?
Books Online says "Cumulative amount of time the driver spent waiting for
replies from the server."
I would account the difference in numbers to the network connection. I
did a quick test on our site. I ran the same SELECT * against a 35000
row table on two servers. One which is hear at our main office in
Stockholm, and one which is at our branch office in Gvle. For the
server here in Stockholm the number was 60, and the server in Gvle
it was 12075. The difference in execution time was 2 seconds in
Stockholm and 28 seconds for the server in Gvle.
I ran both tests from my own machine, which hosts none of the servers in
question.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Tuesday, March 20, 2012

Query example

Hi,

I have to select two different column values from two different tables based on one condition.

Like, I have two tables - orders and fulfillment. I have to select orders.order_id and fulfillment.fulfillment_id for product_id = 2 and date_added = '08/18/06'. The only common fields between the two tables are customer_id,order_id,product_id,date_added.

I tried doing this but I know that won't work because it is returning all the rows from orders and fulfillment.

select o.order_id ,f.fulfillment_id

from orders o,fulfillment f

where o.product_id = 2

and o.date_added = '08/18/06'

and f.product_id = 2

and f.date_added = '08/18/2006'

Can somebody please suggest me how to select records from these two tables based on thte above condition?

Thanks,

Reva,

The details about the relationships of the data between the two tables are a little vague, but let me give it a try:

SELECT
o.order_id,
f.fulfillment_ID
FROM orders AS o
JOIN fulfillment AS f
ON o.order_id = f.order_id
WHERE o.product_id = 2
AND o.date_added = '08/18/06'

You will note that I don't have criteria in the WHERE clause of the f.date_added and f.product_id fields. I am assuming that the date_added and product_id values for a fulfillment are the same as the date_added and product_id values for the related order. If that is the case, you only need to filter the values in one of the tables.

The previous query uses the JOIN and ON clauses to state the join criteria. This is the standars compliant syntax that makes JOINS more accurate and less likely to cause unexpected results.

The problem with your query is that you used the older "legacy" style join syntax of FROM orders,fulfillment. When you do that you MUST state the join criteria (o.order_id = f.order_id) for the rows to match. Otherwise you get what is called a "CROSS JOIN" where every row of the first table is joined to every row of the second table. That is why you are seeing all the extra rows you didn't want. The syntax I used above prevents that from happening by accident because the ON clause is REQUIRED unless CROSS JOIN is explicitly stated.

I hope that helps.

Query error during runtime

I get Invalid object name 'bstr'. when I try to run this query

Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From
(SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,

(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
) As bstr

LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

thanks
Sunitsjoshi (sjoshi@.ingr.com) writes:
> I get Invalid object name 'bstr'. when I try to run this query
> Select distinct c0.oid, c1.Value, c2.Value, c3.Value
> From
> (SELECT oid FROM dbo.COREAttribute
> WHERE CLSID IN (
> '{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
>...
> '{C31DB61A-5221-42CF-9A73-FE76D5158647}'
> )) AS c0 ,
> (select oid, dispid, value
> FROM dbo.COREBSTRAttribute
> WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
> ) As bstr
> LEFT JOIN bstr AS c1
> ON (c0.oid = c1.oid)
> AND c1.dispid = 28
> LEFT JOIN bstr AS c2
> ON (c0.oid = c2.oid)
> AND c2.dispid = 112
> LEFT JOIN bstr AS c3
> ON (c0.oid = c3.oid)
> AND c3.dispid = 192

You cannot refer a virtual table in this way in a query. What you are
trying is a Common Table Expression, which is a new feature in SQL 2005
(culled from ANSI SQL). There you would write:

WITH bstr AS
(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}')
Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From (SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
...
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,
LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

In SQL 2000, you will have to paste in the query in all the three
LEFT JOIN. Or put the stuff into a temp table or table variable first,
so that the query is evaluated only once. In fact this is necessary
in SQL 2005, as the nice syntax only acts as a macro definition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, March 30, 2012

query help

Query Help

Query help

Wednesday, March 28, 2012

query help

Query Help

Query Help

Query Help

Query Help

query help

Query Help

Query Help

Query Help

Monday, March 26, 2012

Query goes from 1 second to 40 minutes with only 1 more record?

Query From Excel

query from different sql servers

Friday, March 23, 2012

Query for multiple tables

Wednesday, March 21, 2012

Query execution time problem

Query execution time

Tuesday, March 20, 2012

Query example

Query error during runtime

query assistance

Blog Archive

About Me