hide system tables: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

How to insert the date

Dear all,
I got a fieldA as datetime (length 8).
When I select in query analyzer, it shows the following value
: 2003-10-27 11:36:27.640
now I get fieldA into a ADODB.recordset in VB with fields
rs_A("fieldA")
And I try to update another table with Field (FieldB) Field B
is also datetime with length 8)
with rs_A ("fieldA") i.e. update tableA set FieldB =
rs_A("fieldA")
The final update statement is query analyzer is :
update tableA set FieldB = 2003-10-27 11:36:27.640
The query analyzer complains incorrect syntax near 11:36 . It
cannot be updated.
How can I insert to FieldB with the complete date and time from
FieldA. Is that something like
update tableA set FieldB = '2003-10-27 11:36:27.640' or
update tableA set FieldB = format(2003-10-27 11:36:27.640)
Please help.
Thanks.hon123456 a écrit :
> Dear all,
> I got a fieldA as datetime (length 8).
> When I select in query analyzer, it shows the following value
> : 2003-10-27 11:36:27.640
> now I get fieldA into a ADODB.recordset in VB with fields
> rs_A("fieldA")
> And I try to update another table with Field (FieldB) Field B
> is also datetime with length 8)
> with rs_A ("fieldA") i.e. update tableA set FieldB =
> rs_A("fieldA")
> The final update statement is query analyzer is :
> update tableA set FieldB = 2003-10-27 11:36:27.640
> The query analyzer complains incorrect syntax near 11:36 . It
> cannot be updated.
> How can I insert to FieldB with the complete date and time from
> FieldA. Is that something like
> update tableA set FieldB = '2003-10-27 11:36:27.640' or
> update tableA set FieldB = format(2003-10-27 11:36:27.640)
1) there si no format about DATETIME SQL type storage it is just
DATETIME (in fact two integers)
2) when casting a string to a DATETIME the only format that is universal
is the ISO short DATETIME format which is :
YYYYMMDD HH:MM:SS.nnn
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose after
4) if you want to use exotic formats about DATETIME as string, set your
session dateformat parameter as above :
SET DATEFORMAT { ymd | mdy | dmy | ydm | myd | ymd }
5) prefer always use a explicit CAST like :
SET DATEFORMAT myd
SELECT CAST('03199722 00:11:22.333' AS DATETIME)
...
A +

> Please help.
> Thanks.
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks SQLpro,
For my query : update tableA set FieldB = 2003-10-27
11:36:27.640
Query Analyzer complains incorrect syntax near 11. What can I do to
make this query work?
Thanks.|||Thanks SQLpro,
Sorry to post again. I do not understand your point 3
suggestion
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose
after
Is that mean I need to change the query as follows:
update tableA set FieldB = 2003-10-27-11:36:27.640
Thanks.|||On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Is that mean I need to change the query as follows:
>update tableA set FieldB = 2003-10-27-11:36:27.640
Put quotes around the date string, and remove the dash between the
date and time:
update tableA set FieldB = '2003-10-27 11:36:27.640'
Roy Harvey
Beacon Falls, CT|||> Put quotes around the date string, and remove the dash between the
> date and time:
... and better yet, put a T between the date and a time part, making int th
e ISO 8601 format which
isn't dependent on any language setting for the login, etc.
(Hi, Roy! I'm happy to see you here :-) .)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:h3lt42hna2q4odv454dpujnrp1hib49od6@.
4ax.com...
> On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
> wrote:
>
> Put quotes around the date string, and remove the dash between the
> date and time:
> update tableA set FieldB = '2003-10-27 11:36:27.640'
> Roy Harvey
> Beacon Falls, CT

How to insert resultset from SP into a table? or use Select * From

I tried this (and other variations) in Query Analyzer - which did not work.
select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
'3/13/06', '3/13/06')
Is there a way to Select/insert the resultset from an SP? How to do this?
Thanks,
RichRich,
Create a temporary or permanent table with the same structure as the result
of the sp. Use:
insert into #t1
exec dbo.usp_p1 ...
AMB
"Rich" wrote:

> I tried this (and other variations) in Query Analyzer - which did not work
.
> select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
> '3/13/06', '3/13/06')
> Is there a way to Select/insert the resultset from an SP? How to do this?
> Thanks,
> Rich
>

How to Insert Raw text into SQL table

HI all,
I have raw text like these:
test1
test2
test3
...
I want to pass these text in to a SP from Query Analyzer as a paremeter and
the SP will handle to import them to a sql table exactly like the format I
passed in: In this case sql table should store:
test1
test2
test3
...
How do you handle things like this.
Thanks,
tomtom d wrote:
> HI all,
> I have raw text like these:
> test1
> test2
> test3
> ...
> I want to pass these text in to a SP from Query Analyzer as a
> paremeter and the SP will handle to import them to a sql table
> exactly like the format I passed in: In this case sql table should
> store:
> test1
> test2
> test3
> ...
> How do you handle things like this.
> Thanks,
> tom
You can try using the DTS Import-Export Wizard or BCP.
David Gugick
Imceda Software
www.imceda.com|||Hi
Try:
create table mytext ( col1 int not null identity(1,1), textval text )
CREATE PROCEDURE InsertMyText ( @.txtval text )
AS
INSERT INTO MyText ( textval ) VALUES ( @.txtval )
EXEC InsertMyText 'text1
text2
text3'
EXEC InsertMyText '
test1
test2
test3'
select * from MyText
"tom d" wrote:

> HI all,
> I have raw text like these:
> test1
> test2
> test3
> ...
> I want to pass these text in to a SP from Query Analyzer as a paremeter an
d
> the SP will handle to import them to a sql table exactly like the format I
> passed in: In this case sql table should store:
> test1
> test2
> test3
> ...
> How do you handle things like this.
> Thanks,
> tom

how to insert picture into a table

how do I do that?
can I do this in query analyzer?Depends on your coding language, you should check in google for stream
+upload +sql server +<yourcodinglanguage>
Or just write your coding language here, perhaps we can provide you with
some interesting links.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
> how do I do that?
> can I do this in query analyzer?
>|||my table pictures is empty.
CREATE TABLE [dbo].[pictures] (
[pid] [int] NULL ,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I use textcopy.exe in dos environment, but I got error.
WHAT HAPPEN? I don't understand ERROR: Row retrieval failed.
I want to insert it, not update it. does that mean textcopy.exe only do
update?
TEXTCOPY Version 1.0
DB-Library version 8.00.2039
Type the SQL Server to connect to: LOCALW
Type your login: brit
Type your password: brit
Type the database: Northwind
Type the table: pictures
Type the text or image column: picture
Type the where clause: where 1=1
Type the file: C:\teaser_head2.jpg
Type the direction ('I' for in, 'O' for out): I
ERROR: Row retrieval failed.
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Depends on your coding language, you should check in google for stream
> +upload +sql server +<yourcodinglanguage>
> Or just write your coding language here, perhaps we can provide you with
> some interesting links.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Jen,
I don't want to use coding language such as C# or java to insert
picture,
I only want to use SQL build-in utility and/or straight SQL query to do
that. so I don't think textcopy is an option since it only does update.
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Depends on your coding language, you should check in google for stream
> +upload +sql server +<yourcodinglanguage>
> Or just write your coding language here, perhaps we can provide you with
> some interesting links.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||You can simply use DDL:
CREATE TABLE [dbo].[pictures] (
[pid] [int] NULL ,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into pictures
values (1,0x000000000101020201010)
So that part is easy. The hard bit is that you have to take your image and
turn it into the hexidecimal format. Can't help you there, I regret.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%238hiZfVtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Jen,
> I don't want to use coding language such as C# or java to insert
> picture,
> I only want to use SQL build-in utility and/or straight SQL query to do
> that. so I don't think textcopy is an option since it only does update.
>
>
>
> "Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote
> in message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
>|||One way to hack around it is to initialize row for textcopy and then update
picture in that row using textcopy.exe
so when you do select picture from the table, you 'll see hexidecimal code.
unfortunately, I can't set image to a local variable in sql server. I guess
I can't avoid application programming then.
thanks
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:efRiZ5VtFHA.1168@.TK2MSFTNGP10.phx.gbl...
> You can simply use DDL:
> CREATE TABLE [dbo].[pictures] (
> [pid] [int] NULL ,
> [picture] [image] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into pictures
> values (1,0x000000000101020201010)
> So that part is easy. The hard bit is that you have to take your image
> and turn it into the hexidecimal format. Can't help you there, I regret.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:%238hiZfVtFHA.3080@.TK2MSFTNGP15.phx.gbl...
>

Wednesday, March 28, 2012

how to insert back salesh into table using query?

Hi ,
i am working with .net and as backhand using mySql.
i have done all configration to use the database drive, my problem is i want to insert a path of my xxx selected file from open dialog box. we all know very well that paht has back salesh like "c:\data\test\filename" that is cause of my problem when ever i insert a path into my table of mysql it accesses all put in different way like "c

atatestfilename".
that is what i hope u understood what i want to know.
any one who has some idea please mail me.
i will thankfull for your's concern.
bye
keep thinking.

Do you mean:

Code Snippet

fileName= "C:\\test\\test.txt";

|||yes but in mySql.
i am waiting.
bye

Monday, March 26, 2012

How to insert a space after each Manager Starts.

hi,
guys
i have query which given below output given below

manager personlevel person name
20851 Howard Wilson1
20852Howard Wilson2
20853Howard Wilson3
20854Howard Wilson4
20855Howard Wilson5
60861Andrew Saxon
60862Andrew Saxon
60863Ian Thompson
60864Ian Thompson
60865Phil Dargan

what i want is after a manager ends i want a null to be inserted for
each of there columns
so that i can distinguish that when a new manager starts

so thatt output looks like this

manager personlevel person name
20851 Howard Wilson1
20852Howard Wilson2
20853Howard Wilson3
20854Howard Wilson4
20855Howard Wilson5
null null null
60861Andrew Saxon
60862Andrew Saxon
60863Ian Thompson
60864Ian Thompson
60865Phil Dargan

Brlliant minds any solution for this..
i know can i loop through the records and do it
and check for a new manager
but i want a better solution ..
give me your ideads folks..

Regards,
Navin MahindrooHi

You don't post the DDL or the current query so it is hard to know what your
SQL is.

Assuming something like:

SELECT Manager, Personlevel, PersonName from Mgmt

You could try (untested)

SELECT Manager, Personlevel, PersonName from
( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
UNION
SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
ORDER BY Id ASC, Manager DESC ) M

John

"Navin" <navinsm2@.rediffmail.com> wrote in message
news:5dc7f532.0306300051.7b6d1f67@.posting.google.c om...
> hi,
> guys
> i have query which given below output given below
> manager personlevel person name
> 2085 1 Howard Wilson1
> 2085 2 Howard Wilson2
> 2085 3 Howard Wilson3
> 2085 4 Howard Wilson4
> 2085 5 Howard Wilson5
> 6086 1 Andrew Saxon
> 6086 2 Andrew Saxon
> 6086 3 Ian Thompson
> 6086 4 Ian Thompson
> 6086 5 Phil Dargan
> what i want is after a manager ends i want a null to be inserted for
> each of there columns
> so that i can distinguish that when a new manager starts
> so thatt output looks like this
> manager personlevel person name
> 2085 1 Howard Wilson1
> 2085 2 Howard Wilson2
> 2085 3 Howard Wilson3
> 2085 4 Howard Wilson4
> 2085 5 Howard Wilson5
> null null null
> 6086 1 Andrew Saxon
> 6086 2 Andrew Saxon
> 6086 3 Ian Thompson
> 6086 4 Ian Thompson
> 6086 5 Phil Dargan
> Brlliant minds any solution for this..
> i know can i loop through the records and do it
> and check for a new manager
> but i want a better solution ..
> give me your ideads folks..
> Regards,
> Navin Mahindroo|||Hi Navin M,

Same other way round.

SELECT 'N' 'GRP_SEP',manager, personlevel ,[person name] FROM
TableName
UNION ALL
SELECT DISTINCT 'Y',manager,NULL,NULL FROM TableName
ORDER BY manager,GRP_SEP ASC

Group seperator is added to explicitly know that row with 'Y' is group
seperator and avoid null conflit if personlevel and name both are
null.

Also note that Manager field has appropriate index on it.

hope this helps you.

Thanks Amit.

navinsm2@.rediffmail.com (Navin) wrote in message news:<5dc7f532.0306300051.7b6d1f67@.posting.google.com>...
> hi,
> guys
> i have query which given below output given below
> manager personlevel person name
> 20851 Howard Wilson1
> 20852Howard Wilson2
> 20853Howard Wilson3
> 20854Howard Wilson4
> 20855Howard Wilson5
> 60861Andrew Saxon
> 60862Andrew Saxon
> 60863Ian Thompson
> 60864Ian Thompson
> 60865Phil Dargan
> what i want is after a manager ends i want a null to be inserted for
> each of there columns
> so that i can distinguish that when a new manager starts
> so thatt output looks like this
> manager personlevel person name
> 20851 Howard Wilson1
> 20852Howard Wilson2
> 20853Howard Wilson3
> 20854Howard Wilson4
> 20855Howard Wilson5
> null null null
> 60861Andrew Saxon
> 60862Andrew Saxon
> 60863Ian Thompson
> 60864Ian Thompson
> 60865Phil Dargan
> Brlliant minds any solution for this..
> i know can i loop through the records and do it
> and check for a new manager
> but i want a better solution ..
> give me your ideads folks..
> Regards,
> Navin Mahindroo|||Hi

Got around to testing it... you can't use the order by in the derived
table!

SELECT Manager, Personlevel, PersonName from
( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
UNION
SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
) M
ORDER BY id, Manager Desc

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f0009d7$0$18490$ed9e5944@.reading.news.pipex. net...
> Hi
> You don't post the DDL or the current query so it is hard to know what
your
> SQL is.
> Assuming something like:
> SELECT Manager, Personlevel, PersonName from Mgmt
> You could try (untested)
> SELECT Manager, Personlevel, PersonName from
> ( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
> UNION
> SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
> ORDER BY Id ASC, Manager DESC ) M
> John
> "Navin" <navinsm2@.rediffmail.com> wrote in message
> news:5dc7f532.0306300051.7b6d1f67@.posting.google.c om...
> > hi,
> > guys
> > i have query which given below output given below
> > manager personlevel person name
> > 2085 1 Howard Wilson1
> > 2085 2 Howard Wilson2
> > 2085 3 Howard Wilson3
> > 2085 4 Howard Wilson4
> > 2085 5 Howard Wilson5
> > 6086 1 Andrew Saxon
> > 6086 2 Andrew Saxon
> > 6086 3 Ian Thompson
> > 6086 4 Ian Thompson
> > 6086 5 Phil Dargan
> > what i want is after a manager ends i want a null to be inserted for
> > each of there columns
> > so that i can distinguish that when a new manager starts
> > so thatt output looks like this
> > manager personlevel person name
> > 2085 1 Howard Wilson1
> > 2085 2 Howard Wilson2
> > 2085 3 Howard Wilson3
> > 2085 4 Howard Wilson4
> > 2085 5 Howard Wilson5
> > null null null
> > 6086 1 Andrew Saxon
> > 6086 2 Andrew Saxon
> > 6086 3 Ian Thompson
> > 6086 4 Ian Thompson
> > 6086 5 Phil Dargan
> > Brlliant minds any solution for this..
> > i know can i loop through the records and do it
> > and check for a new manager
> > but i want a better solution ..
> > give me your ideads folks..
> > Regards,
> > Navin Mahindroo

How to insert a new record (and get it's identity), if a record is not found

What I'm trying to do is write a query (actually it will be a sub-query) that gets the ID of a record using a standard SELECT/WHERE, but if the record is not found then I want to insert a record and return the ID of the inserted record.

So far I've got this (which doesn't work!)

SELECT ISNULL((Select ContactID AS ID FROM [TileManager].[dbo].[Contact] WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs')), (INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs'); SELECT @.@.IDENTITY AS ID))

Any help would be greatly appreciated.

Rob:

Be careful using the @.@.identity function; try SCOPE_IDENTITY() instead. Look it up in the books.

Dave

|||

I don't believe you can embedded the insert statement like that - why aren't you trying something like this

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')
SELECT ID = @.@.IDENTITY

|||

SELECT ID = @.@.identity will almost always work; it can experience problems such as:

-- -
-- Illustrate the difference between @.@.identity and scopy_identity()
--
-- In this example, @.@.identity returns the value of the identity
-- that was inserted by the trigger
-- -
set nocount on
create table dbo.whatA ( what1 integer identity)
go
create table dbo.whatB ( what2 integer identity)
go
insert into whatA default values insert into WhatA default values
insert into whatB default values
insert into whatA default values insert into WhatA default values
insert into whatA default values insert into WhatA default values
--select * from whatA
--select * from whatB
go
create trigger dbo.trI_whatB on whatB
for insert
as
begin
insert into whatA default values
end
go
insert into whatB default values
select @.@.identity as [Incorrect @.@.Identity]
select scope_identity() as [Correct scope_identity()]
select max (what1) as [WhatA Identity] from whatA
select max (what2) as [WhatB Identity] from whatB
go
drop trigger dbo.trI_whatB
go
drop table dbo.whatA
go
drop table dbo.whatB
go

--
-- O U T P U T :
--

-- Incorrect @.@.Identity
-- -
-- 7
-- Correct scope_identity()
-- -
-- 2
-- WhatA Identity
-- --
-- 7
-- WhatB Identity
-- --
-- 2

|||

That's close to what I'm trying to get, but, what I want is the ID of the record if it is found or the ID of the added record if it is not found. The code you've given returns NULL if the record already exists.

Thanks

|||

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')

SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs'

is it acceptable for you to do it as two statements like this?

AWAL

|||

This will do the existance check first and only insert if no record is found...

DECLARE @.ID int

--Get value if existing

SELECT @.ID

FROM [TileManager].[dbo].[Contact]

WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs');

--No value found so insert

IF @.ID IS NULL

BEGIN

INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs');

SELECT @.ID = SCOPE_IDENTITY();

END

--Use ID for something

SELECT @.ID

sql

Friday, March 23, 2012

How to increase timeout?

I'm trying to execute a "DELETE FROM table" command from a query window in
SQL Server 2005 Management Studio, but the query times out after 30 seconds.
How do I increase this timeout value?
OlavOlav,
Follow the menu path:
Tools
Options
Then in the popup box:
Query Execution
SQL Server
General
Set Execution Timeout to 0
RLS
"Olav" <x@.y.com> wrote in message
news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
> I'm trying to execute a "DELETE FROM table" command from a query window in
> SQL Server 2005 Management Studio, but the query times out after 30
> seconds.
> How do I increase this timeout value?
> Olav
>|||The timeout in that dialog is set to 0 and still the query times out in 30
seconds.
Olav
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
> Olav,
> Follow the menu path:
> Tools
> Options
> Then in the popup box:
> Query Execution
> SQL Server
> General
> Set Execution Timeout to 0
> RLS
> "Olav" <x@.y.com> wrote in message
> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>|||"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
I believe it's timing out because some other process is blocking it. Not
because the query itself is taking too long.
Find out what's blocking it and fix that.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>> Olav,
>> Follow the menu path:
>> Tools
>> Options
>> Then in the popup box:
>> Query Execution
>> SQL Server
>> General
>> Set Execution Timeout to 0
>> RLS
>> "Olav" <x@.y.com> wrote in message
>> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Olav,
Greg could be right, but I would not expect a block to cause a timeout
unless the connection timeout period was exceeded. There is no limit to how
long a block can be held, just a limit to how long you are willing to wait
for it.
Could you check your server to see if the "query wait" option is configured
to something other than -1?
RLF
"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>> Olav,
>> Follow the menu path:
>> Tools
>> Options
>> Then in the popup box:
>> Query Execution
>> SQL Server
>> General
>> Set Execution Timeout to 0
>> RLS
>> "Olav" <x@.y.com> wrote in message
>> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>>
>

How to increase timeout?

How to include total in query?

I'll admit that I'm a new MSSQL query writer; however, I'm learning.
I'm curious how I could get a GRAND TOTAL to be included directly under
the [Total] column. Any information would be great!
Thank you!
SELECT Sites.Name [Signups Before 4pm], COUNT(Subscribers.Id) [Total]
FROM Subscribers
JOIN Sites on Sites.Id = Subscribers.SiteId
WHERE Subscribers.DateEntered BETWEEN @.beforestart AND @.beforeend AND
DateOptedOut IS NULL
GROUP BY Sites.Name
ORDER BY Sites.NameTry something like this:
SELECT Sites.Name [Signups Before 4pm], COUNT(Subscribers.Id) [Total]
FROM Subscribers
JOIN Sites on Sites.Id = Subscribers.SiteId
WHERE Subscribers.DateEntered BETWEEN @.beforestart AND @.beforeend AND
DateOptedOut IS NULL
GROUP BY Sites.Name
ORDER BY Sites.Name
COMPUTE SUM(COUNT(Subscribers.Id))|||I really appreciate your help with this. However, I get the following
error:
TDS Protocol error: Unsupported TDS token: 0x88
Thanks again!|||You might also check out the WITH ROLLUP option on GROUP BY
"JeffB" wrote:

> Try something like this:
> SELECT Sites.Name [Signups Before 4pm], COUNT(Subscribers.Id) [Total]
> FROM Subscribers
> JOIN Sites on Sites.Id = Subscribers.SiteId
> WHERE Subscribers.DateEntered BETWEEN @.beforestart AND @.beforeend AND
> DateOptedOut IS NULL
> GROUP BY Sites.Name
> ORDER BY Sites.Name
> COMPUTE SUM(COUNT(Subscribers.Id))
>

how to include the nulls?

Hi, I have the following query stored:

SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @.StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @.EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)

Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...

As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.

The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.

Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.

I'd get

Prod | Qty | Week

1 23 1

1 12 2

2 10 3

3 22 1

3 15 2

3 12 3

3 4 4

Although this looks fine - what I actually need is:

1 23 1

1 12 2

1 0 3

2 0 1

2 0 2

2 10 3

2 0 4

3 22 1

3 15 2

3 12 3

3 4 4

Does that make sense?

Any ideas on how to do this?

Yes, sure that makes sense, but therefore yopu will have to join your "data" table either with a calendar table, or with a subquery returning the weeks that are present for all products to appear in the resultset (cross joining)

HTH, Jens Suessmeyer.

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

ok the calendar tbl sounds the correct option - is something already in my db, or do i have to create it?

if so, any examples of how to do this?

|||Hi,

look here: http://www.aspfaq.com/2519

HTH, Jens Suessmeyer.

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

ok I'm really a novice with all that..

I created it based on just pasting the example they had.. but that's not what I wanted..

I guess what I want is a calendar with the following:

Year, Week and that's it...

that one in that example had a load of extra data which I couldn't make head nor tail of....

|||

ok nearly there.. got my calendar as I want it, but now I've hit a bit of a problem with my join.

If I join my orders tbl by orderdate with the Dt column in my calendar.. then it doesn't return any retults.. here we are:

SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek,
dbo.Calendar.W AS SalesWeek, dbo.Calendar.Y AS SalesYear
FROM dbo.Calendar LEFT OUTER JOIN
dbo.Orders_Retail ON dbo.Calendar.dt = dbo.Orders_Retail.OrderDate INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(dbo.Calendar.D, dbo.Calendar.dt, @.StartDate) <= 0) AND (DATEDIFF(dbo.Calendar.D,
dbo.Calendar.dt, @.EndDate) >= 0)
GROUP BY dbo.Calendar.Y, dbo.Calendar.W, dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, dbo.Calendar.Y, dbo.Calendar.W

Any ideas?

|||

ok I've narrowed it down to the fact that it doesn't like the join.

The one that joins the orderdate in my orders tbl with the dt field in my calendar tbl..

Even though both are datetime fields, both the same.. the only difference I can see between the two in terms of the values they have is that the order date usually has the date and then time, whereas the dt field just has the date...

therefore I was thinking the:

dbo.Calendar ON dbo.Orders_Retail.OrderDate = dbo.Calendar.dt

needs to be changed so that the orderdate vlaue removes it's time from it perhaps?

Any ideas?

James

||||||

Hi!,

I don't think I mad myself clear - I'm not getting an errors -I'm just not getting any results.

|||

anyone got any ideas?

I know exactly what the issue is....

in the calendar the date is held as 1/1/2003.. and up to a point the dates where held in the db like this also, however after a certain date, the time was also held so the date looks like 1/1/2003 00:00:00

This the results that aren't returned.

As I'm doing my JOIN on the dt held in the calendar tbl and then dt in the orders tbl.. as the times are held in the orders tbl also, this means that the join won't work as the calendar tbl will not hold the EXACT date and time held in the orders tbl..

does that make sense?

How do I fix that?

|||

Could you please post you table definitions to get a clear idea on how your tables look like. Seems like the datetime formats in the two columns are different. Schema definition would help in this case.

HTH,

~riyaz~

|||

ok I'm getting somewhere with this now.. however I have another problem.. here's the current query

SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.ProductBrand, SUM(dbo.OrderDetails_Retail.Quantity) AS Qty, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.Orders_Retail INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID INNER JOIN
dbo.Calendar ON CONVERT(datetime, CONVERT(Nvarchar, dbo.Orders_Retail.OrderDate, 102), 102) = CONVERT(datetime, CONVERT(Nvarchar,
dbo.Calendar.dt, 102), 102) RIGHT OUTER JOIN
dbo.Products ON dbo.OrderDetails_Retail.ProductID = dbo.Products.ProductID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> '
Deleted ') AND
(dbo.Orders_Retail.PayStatus <> ' Pending ') AND (dbo.Orders_Retail.OrderStatus <> ' Refunded ') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate,
@.StartDate) <= 0) AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @.EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.ProductBrand
ORDER BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.Products.ProductBrand, dbo.Products.ProductName

What this does is first get the products tbl, join that the orders_detail tbl, joins that the orders tbl which is finally joined to the calendar tbl.

What I'm finding is although it's working as intended, it's still not returning the product that haven't been bought..

I've narrowed this down to the following:

It I just to a Outer join on the products tbl to the order details tbl with no WHERE queries.. this returned what I want,, however as soon as I add the WHERE clause, it loses all the products and only shows the ones that have been purchased.

I would have thoughy my Outer join on the products tbl would get round this problem?

Wednesday, March 21, 2012

How to include a single quote in a sql query

Declare @.Customer varchar(255)
Set @.Customer = Single quotes + customer name + single quotes

Select Customerid from Customer Where name = @.Customer

I have a query written above, but i was not able to add single quotes to the set statement above. Can i know as how to go about it?

Early reply is much appreciated.

Thanks!

You can not write?

set @.Customer = 'customer name'

What is the problem with writing that? Do you get an error?

|||

Nopes, here iam using a variable called "customer name" to which values will be passed in dynamically,

so the query is like

set @.Customer = single quotes + customer name(variable) + single quotes

Hope it is clear, or else if you need more information let me know.

Thanks!

|||

try this....

'''' + Customer Name + ''''

If you want to give the Single Quote on String Litteral you need to use 2 Single Quote Continuously..

-mani

|||set @.Customer = '''' + CustomerName + ''''|||

hey...

char(39) is the ascii for single quotes...

declare @.sql varchar(110)

set @.sql = 'select char(39)+name1+char(39) from test_name'

exec (@.gg)

--assuming test_name has 2 records mak and robin , so the output is

'MAK'

'ROBIN'

|||

edukulla wrote:

Nopes, here iam using a variable called "customer name" to which values will be passed in dynamically,
so the query is like
set @.Customer = single quotes + customer name(variable) + single quotes

Still not clear, a few more questions unless the other replies helped you.

What kind of variable is customer name?
How do you want to execute the SQL statements?
If you are doing this in a programming language, what programming language?

|||

Hello,

If your issue is that you are having difficulties finding a way to deal with character string which may contain one or more single quotes, then the solution is NOT to surround the string with single quotes as a previous user suggested. This will only work if there is in fact onle one single quote in your string such as O'Brian. It will not work if there are multiple quotes such as Here's O'Brian.

SET QUOTED_IDENTIFIER OFF

DECLARE @.s VARCHAR(100)

SET @.s = " Here's O'Brian and some quotes: ''''''''' "

PRINT @.s

Cheers,

Rob

How to improve this SQL statement?

I have a query in MS Access that will select the only record from a table DECISION, given three input parameters: pMonth, pLoansize and pLTV. The record should have the maximal possible value for MONTH, LOAN_SIZE and PERCENTAGE, in this order.

My query is working, but a bit slow since it seems joining four tables. I'd appreicate for any idea about how to improve this query, which is as
follow. Thanks.

------------------------
SELECT distinct C.DECISION_ID, C.DECISION_NAME_ID, C.MONTH, C.LOAN_SIZE, C.percentage, C.ADMIN_FEE_HL_ID, C.PNTY_ID, C.OFFER_RATE_ID
FROM DECISION AS A, DECISION AS B, DECISION AS C, DECISION AS D
WHERE
(D.MONTH = (SELECT max(MONTH) FROM DECISION D WHERE pLoansize>=D.LOAN_SIZEand pMONTH >= D.MONTH AND pLTV >= D.percentage))
AND
(B.LOAN_SIZE= (SELECT max(loan_size) FROM DECISION B WHERE B.MONTH = D.MONTH AND pLoansize>=B.LOAN_SIZEAND pLTV >= B.percentage))
AND
(A.percentage = (SELECT max(percentage) FROM DECISION A WHERE A.MONTH = D.MONTH and A.loan_size=B.LOAN_SIZE
AND pLTV >= A.percentage))
AND C.MONTH = D.MONTH
AND C.LOAN_SIZE= B.loan_size
AND C.percentage = A.percentagetotal shot in the dark, please let me know if this works:SELECT DECISION_ID
, DECISION_NAME_ID
, MONTH
, LOAN_SIZE
, percentage
, ADMIN_FEE_HL_ID
, PNTY_ID
, OFFER_RATE_ID
FROM DECISION AS A
WHERE MONTH = (
SELECT max(MONTH)
FROM DECISION
)
AND LOAN_SIZE = (
SELECT max(loan_size)
FROM DECISION
WHERE MONTH = A.MONTH
)
AND percentage = (
SELECT max(percentage)
FROM DECISION
WHERE MONTH = A.MONTH
and loan_size = a.LOAN_SIZE
)
rudy
http://r937.com/|||Hi r937,

Thanks for the try. But I am afraid that it is not working. Your SQL may end up with no record selected. For example, a simplified DECISION table:

Month, Loan_size, Percentage
8, 100, 70
4, 200, 50
6, 150, 80|||did you try it?

i tried it on your sample of 3 and it gave the row with

8, 100, 70

if you just wanted the max of all three colulmns, that's a totally different query (and a lot simpler, too)

i thought you wanted row integrity

for example, suppose there are 80 rows in the table, of which 20 belong to the highest month

then out of those 20 rows which have the highest month, 6 of those rows have the highest loan_size for that month

then out of those 6 rows which have the highest loan_size for the highest month, one of them has the highest percentage

my query will always return a row, as long as there is at least one row in the table

perhaps you did not explain your problem correctly?

i tried to see what your query was attempting to do, but it's seriously messed up

or maybe i totally misunderstood you

rudy|||Hi Rudy,

Sorry it was my mistake. The query is working now since I missed
out something. Thank you very much.

How to improve this query?

Aliquot table contains columns PatientID, AliquotTypeID, LocationCode,
AliquotNumber. The query is to get the locationCode with minimum
AliquotNumber in a (PatientID and AliquotTypeID) Group.
select locationCode from aliquot a
where a.patientID = 1 and aliquotTypeID = 1 and
a.aliquotNumber = (Select min(aliquotNumber) from aliquot where patientID =
1 and aliquotTypeID= 1 and locationCode is not null)
thanks,Try:
SELECT LOCATIONCODE, MIN(ALIQUOTNUMBER)
FROM ALIQUOT
WHERE PATIENTID = 1 AND ALIQUOTTYPEID = 1 AND LOCATIONCODE IS NOT NULL
GROUP BY LOCATIONCODE
HTH
Jerry
"Caspy" <caspases@.yahoo.com> wrote in message
news:O2fa1rQyFHA.700@.TK2MSFTNGP11.phx.gbl...
> Aliquot table contains columns PatientID, AliquotTypeID, LocationCode,
> AliquotNumber. The query is to get the locationCode with minimum
> AliquotNumber in a (PatientID and AliquotTypeID) Group.
> select locationCode from aliquot a
> where a.patientID = 1 and aliquotTypeID = 1 and
> a.aliquotNumber = (Select min(aliquotNumber) from aliquot where patientID
> = 1 and aliquotTypeID= 1 and locationCode is not null)
> thanks,
>
>|||Thanks for your reply. Because the locationCode is unique for each
AliquotNumber, your query returns multiple rows.
Since the Aliquot table has half million tupples, I just want to limit to 1
visit to this big table when try to retrieve the locationCode with minimum
aliquotNumber in a (patientID and AliquotTypeID) group.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23lXiB3QyFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Try:
> SELECT LOCATIONCODE, MIN(ALIQUOTNUMBER)
> FROM ALIQUOT
> WHERE PATIENTID = 1 AND ALIQUOTTYPEID = 1 AND LOCATIONCODE IS NOT NULL
> GROUP BY LOCATIONCODE
> HTH
> Jerry
> "Caspy" <caspases@.yahoo.com> wrote in message
> news:O2fa1rQyFHA.700@.TK2MSFTNGP11.phx.gbl...
>

how to improve this query, thanks

I have a pretty good db server with four CPUs, it has not any other loads on it, but the following query takes 4ms to return. I use the syscolumns this way quite often, I am not sure why it takes it that long to return, any idea?

select 'master',id,colid,name,xtype,length,xprec,xscale,status from [ablestatic].[dbo].syscolumns
where id=(select id from [ablestatic].[dbo].sysobjects where name='link_data_ezregs')

You might want to run profiler to see whats happening. The query by itself doesnt seem to be doing much. See if this makes any diff (although it shoudnt)

SELECT 'master',id,colid,name,xtype,length,xprec,xscale,status

FROM [ablestatic].[dbo].syscolumns sc

JOIN sysobjects so on sc.id = so.id
WHERE so.name='link_data_ezregs' and so.xtype = 'U'

|||

thanks.

I got the 2 - 4 ms when I run the query in one of my SP and watch it with profiler.

If I run it in query analyzer and in profile I got 15 ms.

|||15ms = milli secs or minutes?|||

millisecond for sure

|||I doubt if theres much you can do. you could use the stored proc as you suggested but even T-SQL should be as efficient, if its a single T-SQL statement.|||Check the execution plan when the query is executed inside/outside a sp, it may be caused by parameter sniffing, here are some related articles:

http://forums.asp.net/2/1377161/ShowThread.aspx

Understanding Plan Guides

How to improve the query performance on cubes?

Hi,all experts here,

How could we effectively improve the query performance on cubes from a client? Can we create indexes on them? Or if not, what else can we try?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

There are a lot of things to try - partitioning, aggregations.... too much to discuss in a forums answer. Your best bet is to read the AS performance guide:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

Chris

|||

Thanks a lot.

Best regards,

sql

How to improve the query performance for my reports?

Hi, all here,

Thank you very much for your kind attention.

I am having a problem with the performance of my reports. The data for my reports is retrieved from two large transaction joined tables thus resulting in the poor qeury performance for my reports. How can I effectively improve the query performance? It is that I'd better to create a data view for the report data to retrive the data for the reports based on the data view? Or what is the best pratice for it?

Thanks a lot in advance for any guidance and help.

With best regards,

Yours sincerely,

This is the same as tuning any SQL. Drop the SQL from your dataset into SQL Server Management Studio in a New Query and check the Include Actual Execution Plan. Run the query and look at the execution plan. If you see any 'Table Scans' look for ways to eliminate them (different join criteria, different select criteria, create new indexes, etc). If you are using parameters you may need to 'hard code them' or declare SQL variables for them (I prefer the later so the query can be dropped right back into reporting services once I get it tuned).

|||

Hi, Lonnie, thank you very much for your advices.

With best regards,

Yours sincerely,

|||Further to Lonnie's good advice I would also suggest as a general good practice returning as little data to RS as possible. Utilise parameters to filter the query, use a group by to presummarise the data if possible. RS will be performign further processing of your data in ReportServerTempDB for report formatting and paging so reducing the amount of data it needs to deal with can have a huge impact on performance.

How to improve the query performance for my reports?

Hi, all here,

Thank you very much for your kind attention.

Thanks a lot in advance for any guidance and help.

With best regards,

Yours sincerely,

|||

Hi, Lonnie, thank you very much for your advices.

With best regards,

Yours sincerely,

How to improve the performance,

Hi *.*
I've a problem with my query, perhaps you professionals can kindly
Help me to overcome this, let me explain the problem, We have an
Inventory software with about 8500 of Goods (we stored it in table
Called tblPart), every input/output of these Goods saves in a Table
Called tblInvRate, we have a field called Type, if it's input then
Type=1, if that's output the Type=-1, Qty is the number of input or
Output.
Now with the following query we get the remain of Goods in the
Inventory:
SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
Rate.Type) AS PriorRemain
FROM dbo.tblInvRate Rate INNER JOIN
dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
WHERE Rate.[Date] < '850515'
GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
HAVING SUM(Rate.Qty * Rate.Type)>0
This query takes about 1 sec to execute,
In tblInvRate we have a field called IsRated and Rate, Rated and
IsRated sets in a sp named prRatingInv which calculate the rate of the
Goods and...
We need to have the last rate of Goods in the remain query so we use
This:
SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
SUM(Rate.Qty * Rate.Type) AS PriorRemain,
(SELECT TOP 1 Rate
FROM dbo.tblInvRate
WHERE (fkPart = dbo.tblPart.pkCode
AND [Date]<='850515' And IsRated = 1)
ORDER BY [Date] DESC, [Time] DESC) AS
Rate
FROM dbo.tblInvRate Rate INNER JOIN
dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
WHERE Rate.[Date] < '850515'
GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
HAVING SUM(Rate.Qty * Rate.Type)>0
Above query may take about 40 to 80 secs to execute!!! (Total number of
rows in the tblInvRate is about 1.000.000 records)
H've run the Index Wizard but it can't find any more index on the
Tables, now would you please help me in this situation,
here is the schema of these tables:
CREATE TABLE [tblPart] (
[pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
NULL ,
[fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
(1),
[Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
[TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
NULL ,
[OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
,
[fkPartAccGroup] [int] NULL ,
CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
(
[pkCode]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblInvRate] (
[pkID] [int] NOT NULL ,
[Source] [int] NOT NULL ,
[Dest] [int] NOT NULL ,
[fkInvHdr] [int] NULL ,
[fkInvItm] [int] NULL ,
[fkChangePart] [int] NULL ,
[fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
NULL ,
[Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
[Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
[Qty] [int] NOT NULL ,
[Rate] [decimal](24, 4) NULL ,
[Amount] [decimal](24, 0) NULL ,
[OldRate] [decimal](24, 4) NULL ,
[MainRate] [decimal](24, 4) NULL ,
[Type] [int] NOT NULL ,
[Code] [int] NOT NULL ,
[IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
DEFAULT (0),
[fkCurrency] [int] NULL ,
[CurrencyRate] [int] NULL ,
[CurrencyAmount] [decimal](24, 0) NULL ,
[fkShop] [int] NULL ,
[fkPerson] [int] NULL ,
[RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
(0),
[IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
(0),
[fkAccVchHdr] [int] NULL ,
[AccTempNum] [int] NULL ,
CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
(
[pkID],
[Source]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
(
[fkChangePart],
[Source]
) REFERENCES [tblChangePart] (
[pkID],
[Source]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
(
[fkInvHdr],
[Source]
) REFERENCES [tblInvHdr] (
[pkID],
[Source]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
Thanks in advance,
Waiting for your tips and advices,
HamedHamed,
Try using the correlated query after the remain of Goods in the Inventory
has been calculated.
select
a.*,
(
SELECT TOP 1
b.Rate
FROM
dbo.tblInvRate as b
WHERE
b.fkPart = a.pkCode
AND b.[Date] <= '850515'
And b.IsRated = 1
) as Rate
from
(
SELECT
dbo.tblPart.pkCode,
dbo.tblPart.Title,
SUM(Rate.Qty * Rate.Type) AS PriorRemain
FROM
dbo.tblInvRate as Rate
INNER JOIN
dbo.tblPart
ON Rate.fkPart = dbo.tblPart.pkCode
WHERE
Rate.[Date] < '850515'
GROUP BY
dbo.tblPart.pkCode, dbo.tblPart.Title
HAVING
SUM(Rate.Qty * Rate.Type)>0
) as a
go
AMB
"Hamed" wrote:
> Hi *.*
> I've a problem with my query, perhaps you professionals can kindly
> Help me to overcome this, let me explain the problem, We have an
> Inventory software with about 8500 of Goods (we stored it in table
> Called tblPart), every input/output of these Goods saves in a Table
> Called tblInvRate, we have a field called Type, if it's input then
> Type=1, if that's output the Type=-1, Qty is the number of input or
> Output.
> Now with the following query we get the remain of Goods in the
> Inventory:
> SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> Rate.Type) AS PriorRemain
> FROM dbo.tblInvRate Rate INNER JOIN
> dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> WHERE Rate.[Date] < '850515'
> GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> HAVING SUM(Rate.Qty * Rate.Type)>0
> This query takes about 1 sec to execute,
> In tblInvRate we have a field called IsRated and Rate, Rated and
> IsRated sets in a sp named prRatingInv which calculate the rate of the
> Goods and...
> We need to have the last rate of Goods in the remain query so we use
> This:
> SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> (SELECT TOP 1 Rate
> FROM dbo.tblInvRate
> WHERE (fkPart = dbo.tblPart.pkCode
> AND [Date]<='850515' And IsRated = 1)
> ORDER BY [Date] DESC, [Time] DESC) AS
> Rate
> FROM dbo.tblInvRate Rate INNER JOIN
> dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> WHERE Rate.[Date] < '850515'
> GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> HAVING SUM(Rate.Qty * Rate.Type)>0
> Above query may take about 40 to 80 secs to execute!!! (Total number of
> rows in the tblInvRate is about 1.000.000 records)
> H've run the Index Wizard but it can't find any more index on the
> Tables, now would you please help me in this situation,
> here is the schema of these tables:
> CREATE TABLE [tblPart] (
> [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> NULL ,
> [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> NULL ,
> [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> (1),
> [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> NULL ,
> [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> ,
> [fkPartAccGroup] [int] NULL ,
> CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> (
> [pkCode]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblInvRate] (
> [pkID] [int] NOT NULL ,
> [Source] [int] NOT NULL ,
> [Dest] [int] NOT NULL ,
> [fkInvHdr] [int] NULL ,
> [fkInvItm] [int] NULL ,
> [fkChangePart] [int] NULL ,
> [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> NULL ,
> [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> [Qty] [int] NOT NULL ,
> [Rate] [decimal](24, 4) NULL ,
> [Amount] [decimal](24, 0) NULL ,
> [OldRate] [decimal](24, 4) NULL ,
> [MainRate] [decimal](24, 4) NULL ,
> [Type] [int] NOT NULL ,
> [Code] [int] NOT NULL ,
> [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> DEFAULT (0),
> [fkCurrency] [int] NULL ,
> [CurrencyRate] [int] NULL ,
> [CurrencyAmount] [decimal](24, 0) NULL ,
> [fkShop] [int] NULL ,
> [fkPerson] [int] NULL ,
> [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> (0),
> [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> (0),
> [fkAccVchHdr] [int] NULL ,
> [AccTempNum] [int] NULL ,
> CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> (
> [pkID],
> [Source]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> (
> [fkChangePart],
> [Source]
> ) REFERENCES [tblChangePart] (
> [pkID],
> [Source]
> ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> (
> [fkInvHdr],
> [Source]
> ) REFERENCES [tblInvHdr] (
> [pkID],
> [Source]
> ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> ) ON [PRIMARY]
> GO
>
> Thanks in advance,
> Waiting for your tips and advices,
> Hamed
>|||Thanks for your reply, it take 135 secs to execute,
Alejandro Mesa wrote:
> Hamed,
> Try using the correlated query after the remain of Goods in the Inventory
> has been calculated.
> select
> a.*,
> (
> SELECT TOP 1
> b.Rate
> FROM
> dbo.tblInvRate as b
> WHERE
> b.fkPart = a.pkCode
> AND b.[Date] <= '850515'
> And b.IsRated = 1
> ) as Rate
> from
> (
> SELECT
> dbo.tblPart.pkCode,
> dbo.tblPart.Title,
> SUM(Rate.Qty * Rate.Type) AS PriorRemain
> FROM
> dbo.tblInvRate as Rate
> INNER JOIN
> dbo.tblPart
> ON Rate.fkPart = dbo.tblPart.pkCode
> WHERE
> Rate.[Date] < '850515'
> GROUP BY
> dbo.tblPart.pkCode, dbo.tblPart.Title
> HAVING
> SUM(Rate.Qty * Rate.Type)>0
> ) as a
> go
>
> AMB
> "Hamed" wrote:
> > Hi *.*
> >
> > I've a problem with my query, perhaps you professionals can kindly
> > Help me to overcome this, let me explain the problem, We have an
> > Inventory software with about 8500 of Goods (we stored it in table
> > Called tblPart), every input/output of these Goods saves in a Table
> > Called tblInvRate, we have a field called Type, if it's input then
> > Type=1, if that's output the Type=-1, Qty is the number of input or
> > Output.
> >
> > Now with the following query we get the remain of Goods in the
> > Inventory:
> >
> > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> > Rate.Type) AS PriorRemain
> > FROM dbo.tblInvRate Rate INNER JOIN
> > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > WHERE Rate.[Date] < '850515'
> > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > HAVING SUM(Rate.Qty * Rate.Type)>0
> >
> > This query takes about 1 sec to execute,
> >
> > In tblInvRate we have a field called IsRated and Rate, Rated and
> > IsRated sets in a sp named prRatingInv which calculate the rate of the
> > Goods and...
> >
> > We need to have the last rate of Goods in the remain query so we use
> > This:
> >
> > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> > SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> > (SELECT TOP 1 Rate
> > FROM dbo.tblInvRate
> > WHERE (fkPart = dbo.tblPart.pkCode
> > AND [Date]<='850515' And IsRated = 1)
> > ORDER BY [Date] DESC, [Time] DESC) AS
> > Rate
> > FROM dbo.tblInvRate Rate INNER JOIN
> > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > WHERE Rate.[Date] < '850515'
> > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > HAVING SUM(Rate.Qty * Rate.Type)>0
> >
> > Above query may take about 40 to 80 secs to execute!!! (Total number of
> > rows in the tblInvRate is about 1.000.000 records)
> >
> > H've run the Index Wizard but it can't find any more index on the
> > Tables, now would you please help me in this situation,
> >
> > here is the schema of these tables:
> >
> > CREATE TABLE [tblPart] (
> > [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > NULL ,
> > [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > NULL ,
> > [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> > (1),
> > [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> > [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> > NULL ,
> > [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> > ,
> > [fkPartAccGroup] [int] NULL ,
> > CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> > (
> > [pkCode]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> >
> > CREATE TABLE [tblInvRate] (
> > [pkID] [int] NOT NULL ,
> > [Source] [int] NOT NULL ,
> > [Dest] [int] NOT NULL ,
> > [fkInvHdr] [int] NULL ,
> > [fkInvItm] [int] NULL ,
> > [fkChangePart] [int] NULL ,
> > [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > NULL ,
> > [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > [Qty] [int] NOT NULL ,
> > [Rate] [decimal](24, 4) NULL ,
> > [Amount] [decimal](24, 0) NULL ,
> > [OldRate] [decimal](24, 4) NULL ,
> > [MainRate] [decimal](24, 4) NULL ,
> > [Type] [int] NOT NULL ,
> > [Code] [int] NOT NULL ,
> > [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> > DEFAULT (0),
> > [fkCurrency] [int] NULL ,
> > [CurrencyRate] [int] NULL ,
> > [CurrencyAmount] [decimal](24, 0) NULL ,
> > [fkShop] [int] NULL ,
> > [fkPerson] [int] NULL ,
> > [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> > (0),
> > [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> > (0),
> > [fkAccVchHdr] [int] NULL ,
> > [AccTempNum] [int] NULL ,
> > CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> > (
> > [pkID],
> > [Source]
> > ) ON [PRIMARY] ,
> > CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> > (
> > [fkChangePart],
> > [Source]
> > ) REFERENCES [tblChangePart] (
> > [pkID],
> > [Source]
> > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> > CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> > (
> > [fkInvHdr],
> > [Source]
> > ) REFERENCES [tblInvHdr] (
> > [pkID],
> > [Source]
> > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> > ) ON [PRIMARY]
> > GO
> >
> >
> > Thanks in advance,
> > Waiting for your tips and advices,
> > Hamed
> >
> >|||Hamed,
can you post the execution plan?
AMB
"Hamed" wrote:
> Thanks for your reply, it take 135 secs to execute,
> Alejandro Mesa wrote:
> > Hamed,
> >
> > Try using the correlated query after the remain of Goods in the Inventory
> > has been calculated.
> >
> > select
> > a.*,
> > (
> > SELECT TOP 1
> > b.Rate
> > FROM
> > dbo.tblInvRate as b
> > WHERE
> > b.fkPart = a.pkCode
> > AND b.[Date] <= '850515'
> > And b.IsRated = 1
> > ) as Rate
> > from
> > (
> > SELECT
> > dbo.tblPart.pkCode,
> > dbo.tblPart.Title,
> > SUM(Rate.Qty * Rate.Type) AS PriorRemain
> > FROM
> > dbo.tblInvRate as Rate
> > INNER JOIN
> > dbo.tblPart
> > ON Rate.fkPart = dbo.tblPart.pkCode
> > WHERE
> > Rate.[Date] < '850515'
> > GROUP BY
> > dbo.tblPart.pkCode, dbo.tblPart.Title
> > HAVING
> > SUM(Rate.Qty * Rate.Type)>0
> > ) as a
> > go
> >
> >
> > AMB
> >
> > "Hamed" wrote:
> >
> > > Hi *.*
> > >
> > > I've a problem with my query, perhaps you professionals can kindly
> > > Help me to overcome this, let me explain the problem, We have an
> > > Inventory software with about 8500 of Goods (we stored it in table
> > > Called tblPart), every input/output of these Goods saves in a Table
> > > Called tblInvRate, we have a field called Type, if it's input then
> > > Type=1, if that's output the Type=-1, Qty is the number of input or
> > > Output.
> > >
> > > Now with the following query we get the remain of Goods in the
> > > Inventory:
> > >
> > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> > > Rate.Type) AS PriorRemain
> > > FROM dbo.tblInvRate Rate INNER JOIN
> > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > WHERE Rate.[Date] < '850515'
> > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > >
> > > This query takes about 1 sec to execute,
> > >
> > > In tblInvRate we have a field called IsRated and Rate, Rated and
> > > IsRated sets in a sp named prRatingInv which calculate the rate of the
> > > Goods and...
> > >
> > > We need to have the last rate of Goods in the remain query so we use
> > > This:
> > >
> > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> > > SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> > > (SELECT TOP 1 Rate
> > > FROM dbo.tblInvRate
> > > WHERE (fkPart = dbo.tblPart.pkCode
> > > AND [Date]<='850515' And IsRated = 1)
> > > ORDER BY [Date] DESC, [Time] DESC) AS
> > > Rate
> > > FROM dbo.tblInvRate Rate INNER JOIN
> > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > WHERE Rate.[Date] < '850515'
> > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > >
> > > Above query may take about 40 to 80 secs to execute!!! (Total number of
> > > rows in the tblInvRate is about 1.000.000 records)
> > >
> > > H've run the Index Wizard but it can't find any more index on the
> > > Tables, now would you please help me in this situation,
> > >
> > > here is the schema of these tables:
> > >
> > > CREATE TABLE [tblPart] (
> > > [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > NULL ,
> > > [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > NULL ,
> > > [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> > > (1),
> > > [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> > > [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> > > NULL ,
> > > [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> > > ,
> > > [fkPartAccGroup] [int] NULL ,
> > > CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> > > (
> > > [pkCode]
> > > ) ON [PRIMARY]
> > > ) ON [PRIMARY]
> > > GO
> > >
> > >
> > > CREATE TABLE [tblInvRate] (
> > > [pkID] [int] NOT NULL ,
> > > [Source] [int] NOT NULL ,
> > > [Dest] [int] NOT NULL ,
> > > [fkInvHdr] [int] NULL ,
> > > [fkInvItm] [int] NULL ,
> > > [fkChangePart] [int] NULL ,
> > > [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > NULL ,
> > > [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > [Qty] [int] NOT NULL ,
> > > [Rate] [decimal](24, 4) NULL ,
> > > [Amount] [decimal](24, 0) NULL ,
> > > [OldRate] [decimal](24, 4) NULL ,
> > > [MainRate] [decimal](24, 4) NULL ,
> > > [Type] [int] NOT NULL ,
> > > [Code] [int] NOT NULL ,
> > > [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> > > DEFAULT (0),
> > > [fkCurrency] [int] NULL ,
> > > [CurrencyRate] [int] NULL ,
> > > [CurrencyAmount] [decimal](24, 0) NULL ,
> > > [fkShop] [int] NULL ,
> > > [fkPerson] [int] NULL ,
> > > [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> > > (0),
> > > [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> > > (0),
> > > [fkAccVchHdr] [int] NULL ,
> > > [AccTempNum] [int] NULL ,
> > > CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> > > (
> > > [pkID],
> > > [Source]
> > > ) ON [PRIMARY] ,
> > > CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> > > (
> > > [fkChangePart],
> > > [Source]
> > > ) REFERENCES [tblChangePart] (
> > > [pkID],
> > > [Source]
> > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> > > CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> > > (
> > > [fkInvHdr],
> > > [Source]
> > > ) REFERENCES [tblInvHdr] (
> > > [pkID],
> > > [Source]
> > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> > > ) ON [PRIMARY]
> > > GO
> > >
> > >
> > > Thanks in advance,
> > > Waiting for your tips and advices,
> > > Hamed
> > >
> > >
>|||I don't know how to post the execution plan to this group,
Alejandro Mesa wrote:
> Hamed,
> can you post the execution plan?
>
> AMB
> "Hamed" wrote:
> > Thanks for your reply, it take 135 secs to execute,
> >
> > Alejandro Mesa wrote:
> > > Hamed,
> > >
> > > Try using the correlated query after the remain of Goods in the Inventory
> > > has been calculated.
> > >
> > > select
> > > a.*,
> > > (
> > > SELECT TOP 1
> > > b.Rate
> > > FROM
> > > dbo.tblInvRate as b
> > > WHERE
> > > b.fkPart = a.pkCode
> > > AND b.[Date] <= '850515'
> > > And b.IsRated = 1
> > > ) as Rate
> > > from
> > > (
> > > SELECT
> > > dbo.tblPart.pkCode,
> > > dbo.tblPart.Title,
> > > SUM(Rate.Qty * Rate.Type) AS PriorRemain
> > > FROM
> > > dbo.tblInvRate as Rate
> > > INNER JOIN
> > > dbo.tblPart
> > > ON Rate.fkPart = dbo.tblPart.pkCode
> > > WHERE
> > > Rate.[Date] < '850515'
> > > GROUP BY
> > > dbo.tblPart.pkCode, dbo.tblPart.Title
> > > HAVING
> > > SUM(Rate.Qty * Rate.Type)>0
> > > ) as a
> > > go
> > >
> > >
> > > AMB
> > >
> > > "Hamed" wrote:
> > >
> > > > Hi *.*
> > > >
> > > > I've a problem with my query, perhaps you professionals can kindly
> > > > Help me to overcome this, let me explain the problem, We have an
> > > > Inventory software with about 8500 of Goods (we stored it in table
> > > > Called tblPart), every input/output of these Goods saves in a Table
> > > > Called tblInvRate, we have a field called Type, if it's input then
> > > > Type=1, if that's output the Type=-1, Qty is the number of input or
> > > > Output.
> > > >
> > > > Now with the following query we get the remain of Goods in the
> > > > Inventory:
> > > >
> > > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> > > > Rate.Type) AS PriorRemain
> > > > FROM dbo.tblInvRate Rate INNER JOIN
> > > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > > WHERE Rate.[Date] < '850515'
> > > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > > >
> > > > This query takes about 1 sec to execute,
> > > >
> > > > In tblInvRate we have a field called IsRated and Rate, Rated and
> > > > IsRated sets in a sp named prRatingInv which calculate the rate of the
> > > > Goods and...
> > > >
> > > > We need to have the last rate of Goods in the remain query so we use
> > > > This:
> > > >
> > > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> > > > SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> > > > (SELECT TOP 1 Rate
> > > > FROM dbo.tblInvRate
> > > > WHERE (fkPart = dbo.tblPart.pkCode
> > > > AND [Date]<='850515' And IsRated = 1)
> > > > ORDER BY [Date] DESC, [Time] DESC) AS
> > > > Rate
> > > > FROM dbo.tblInvRate Rate INNER JOIN
> > > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > > WHERE Rate.[Date] < '850515'
> > > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > > >
> > > > Above query may take about 40 to 80 secs to execute!!! (Total number of
> > > > rows in the tblInvRate is about 1.000.000 records)
> > > >
> > > > H've run the Index Wizard but it can't find any more index on the
> > > > Tables, now would you please help me in this situation,
> > > >
> > > > here is the schema of these tables:
> > > >
> > > > CREATE TABLE [tblPart] (
> > > > [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > NULL ,
> > > > [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > NULL ,
> > > > [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> > > > (1),
> > > > [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> > > > [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> > > > NULL ,
> > > > [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> > > > ,
> > > > [fkPartAccGroup] [int] NULL ,
> > > > CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> > > > (
> > > > [pkCode]
> > > > ) ON [PRIMARY]
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > >
> > > > CREATE TABLE [tblInvRate] (
> > > > [pkID] [int] NOT NULL ,
> > > > [Source] [int] NOT NULL ,
> > > > [Dest] [int] NOT NULL ,
> > > > [fkInvHdr] [int] NULL ,
> > > > [fkInvItm] [int] NULL ,
> > > > [fkChangePart] [int] NULL ,
> > > > [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > NULL ,
> > > > [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > > [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > > [Qty] [int] NOT NULL ,
> > > > [Rate] [decimal](24, 4) NULL ,
> > > > [Amount] [decimal](24, 0) NULL ,
> > > > [OldRate] [decimal](24, 4) NULL ,
> > > > [MainRate] [decimal](24, 4) NULL ,
> > > > [Type] [int] NOT NULL ,
> > > > [Code] [int] NOT NULL ,
> > > > [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> > > > DEFAULT (0),
> > > > [fkCurrency] [int] NULL ,
> > > > [CurrencyRate] [int] NULL ,
> > > > [CurrencyAmount] [decimal](24, 0) NULL ,
> > > > [fkShop] [int] NULL ,
> > > > [fkPerson] [int] NULL ,
> > > > [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> > > > (0),
> > > > [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> > > > (0),
> > > > [fkAccVchHdr] [int] NULL ,
> > > > [AccTempNum] [int] NULL ,
> > > > CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> > > > (
> > > > [pkID],
> > > > [Source]
> > > > ) ON [PRIMARY] ,
> > > > CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> > > > (
> > > > [fkChangePart],
> > > > [Source]
> > > > ) REFERENCES [tblChangePart] (
> > > > [pkID],
> > > > [Source]
> > > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> > > > CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> > > > (
> > > > [fkInvHdr],
> > > > [Source]
> > > > ) REFERENCES [tblInvHdr] (
> > > > [pkID],
> > > > [Source]
> > > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > >
> > > > Thanks in advance,
> > > > Waiting for your tips and advices,
> > > > Hamed
> > > >
> > > >
> >
> >|||Hamed,
Use "set showplan_text on" to get the execution plan as text.
set showplan_text on
go
select ...
go
set showplan_text off
go
AMB
"Hamed" wrote:
> I don't know how to post the execution plan to this group,
> Alejandro Mesa wrote:
> > Hamed,
> >
> > can you post the execution plan?
> >
> >
> > AMB
> >
> > "Hamed" wrote:
> >
> > > Thanks for your reply, it take 135 secs to execute,
> > >
> > > Alejandro Mesa wrote:
> > > > Hamed,
> > > >
> > > > Try using the correlated query after the remain of Goods in the Inventory
> > > > has been calculated.
> > > >
> > > > select
> > > > a.*,
> > > > (
> > > > SELECT TOP 1
> > > > b.Rate
> > > > FROM
> > > > dbo.tblInvRate as b
> > > > WHERE
> > > > b.fkPart = a.pkCode
> > > > AND b.[Date] <= '850515'
> > > > And b.IsRated = 1
> > > > ) as Rate
> > > > from
> > > > (
> > > > SELECT
> > > > dbo.tblPart.pkCode,
> > > > dbo.tblPart.Title,
> > > > SUM(Rate.Qty * Rate.Type) AS PriorRemain
> > > > FROM
> > > > dbo.tblInvRate as Rate
> > > > INNER JOIN
> > > > dbo.tblPart
> > > > ON Rate.fkPart = dbo.tblPart.pkCode
> > > > WHERE
> > > > Rate.[Date] < '850515'
> > > > GROUP BY
> > > > dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > HAVING
> > > > SUM(Rate.Qty * Rate.Type)>0
> > > > ) as a
> > > > go
> > > >
> > > >
> > > > AMB
> > > >
> > > > "Hamed" wrote:
> > > >
> > > > > Hi *.*
> > > > >
> > > > > I've a problem with my query, perhaps you professionals can kindly
> > > > > Help me to overcome this, let me explain the problem, We have an
> > > > > Inventory software with about 8500 of Goods (we stored it in table
> > > > > Called tblPart), every input/output of these Goods saves in a Table
> > > > > Called tblInvRate, we have a field called Type, if it's input then
> > > > > Type=1, if that's output the Type=-1, Qty is the number of input or
> > > > > Output.
> > > > >
> > > > > Now with the following query we get the remain of Goods in the
> > > > > Inventory:
> > > > >
> > > > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> > > > > Rate.Type) AS PriorRemain
> > > > > FROM dbo.tblInvRate Rate INNER JOIN
> > > > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > > > WHERE Rate.[Date] < '850515'
> > > > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > > > >
> > > > > This query takes about 1 sec to execute,
> > > > >
> > > > > In tblInvRate we have a field called IsRated and Rate, Rated and
> > > > > IsRated sets in a sp named prRatingInv which calculate the rate of the
> > > > > Goods and...
> > > > >
> > > > > We need to have the last rate of Goods in the remain query so we use
> > > > > This:
> > > > >
> > > > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> > > > > SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> > > > > (SELECT TOP 1 Rate
> > > > > FROM dbo.tblInvRate
> > > > > WHERE (fkPart = dbo.tblPart.pkCode
> > > > > AND [Date]<='850515' And IsRated = 1)
> > > > > ORDER BY [Date] DESC, [Time] DESC) AS
> > > > > Rate
> > > > > FROM dbo.tblInvRate Rate INNER JOIN
> > > > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > > > WHERE Rate.[Date] < '850515'
> > > > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > > > >
> > > > > Above query may take about 40 to 80 secs to execute!!! (Total number of
> > > > > rows in the tblInvRate is about 1.000.000 records)
> > > > >
> > > > > H've run the Index Wizard but it can't find any more index on the
> > > > > Tables, now would you please help me in this situation,
> > > > >
> > > > > here is the schema of these tables:
> > > > >
> > > > > CREATE TABLE [tblPart] (
> > > > > [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > > NULL ,
> > > > > [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > > NULL ,
> > > > > [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> > > > > (1),
> > > > > [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> > > > > [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> > > > > NULL ,
> > > > > [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> > > > > ,
> > > > > [fkPartAccGroup] [int] NULL ,
> > > > > CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> > > > > (
> > > > > [pkCode]
> > > > > ) ON [PRIMARY]
> > > > > ) ON [PRIMARY]
> > > > > GO
> > > > >
> > > > >
> > > > > CREATE TABLE [tblInvRate] (
> > > > > [pkID] [int] NOT NULL ,
> > > > > [Source] [int] NOT NULL ,
> > > > > [Dest] [int] NOT NULL ,
> > > > > [fkInvHdr] [int] NULL ,
> > > > > [fkInvItm] [int] NULL ,
> > > > > [fkChangePart] [int] NULL ,
> > > > > [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > > NULL ,
> > > > > [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > > > [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > > > [Qty] [int] NOT NULL ,
> > > > > [Rate] [decimal](24, 4) NULL ,
> > > > > [Amount] [decimal](24, 0) NULL ,
> > > > > [OldRate] [decimal](24, 4) NULL ,
> > > > > [MainRate] [decimal](24, 4) NULL ,
> > > > > [Type] [int] NOT NULL ,
> > > > > [Code] [int] NOT NULL ,
> > > > > [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> > > > > DEFAULT (0),
> > > > > [fkCurrency] [int] NULL ,
> > > > > [CurrencyRate] [int] NULL ,
> > > > > [CurrencyAmount] [decimal](24, 0) NULL ,
> > > > > [fkShop] [int] NULL ,
> > > > > [fkPerson] [int] NULL ,
> > > > > [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> > > > > (0),
> > > > > [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> > > > > (0),
> > > > > [fkAccVchHdr] [int] NULL ,
> > > > > [AccTempNum] [int] NULL ,
> > > > > CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> > > > > (
> > > > > [pkID],
> > > > > [Source]
> > > > > ) ON [PRIMARY] ,
> > > > > CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> > > > > (
> > > > > [fkChangePart],
> > > > > [Source]
> > > > > ) REFERENCES [tblChangePart] (
> > > > > [pkID],
> > > > > [Source]
> > > > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> > > > > CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> > > > > (
> > > > > [fkInvHdr],
> > > > > [Source]
> > > > > ) REFERENCES [tblInvHdr] (
> > > > > [pkID],
> > > > > [Source]
> > > > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> > > > > ) ON [PRIMARY]
> > > > > GO
> > > > >
> > > > >
> > > > > Thanks in advance,
> > > > > Waiting for your tips and advices,
> > > > > Hamed
> > > > >
> > > > >
> > >
> > >
>|||This is yours:
|--Compute Scalar(DEFINE:([b].[Rate]=[b].[Rate]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([tblPart].[pkCode]))
|--Filter(WHERE:([Expr1003]>0))
| |--Merge Join(Inner Join,
MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
| |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
| | |--Index
Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
| |--Index
Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
|--Top(1)
|--Filter(WHERE:(Convert([b].[IsRated])=1))
|--Bookmark Lookup(BOOKMARK:([Bmk1004]),
OBJECT:([PosKish].[dbo].[tblInvRate] AS [b]) WITH PREFETCH)
|--Index
Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [b]),
SEEK:([b].[fkPart]=[tblPart].[pkCode] AND [b].[Date] <= '850515')
ORDERED FORWARD)
and this is mine:
|--Compute Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([tblPart].[pkCode]))
|--Filter(WHERE:([Expr1003]>0))
| |--Merge Join(Inner Join,
MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
| |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
| | |--Index
Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
| |--Index
Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
|--Compute
Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
|--Sort(TOP 1, ORDER BY:([tblInvRate].[Date] DESC,
[tblInvRate].[Time] DESC))
|--Filter(WHERE:(Convert([tblInvRate].[IsRated])=1))
|--Bookmark Lookup(BOOKMARK:([Bmk1004]),
OBJECT:([PosKish].[dbo].[tblInvRate]) WITH PREFETCH)
|--Index
Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11]),
SEEK:([tblInvRate].[fkPart]=[tblPart].[pkCode] AND [tblInvRate].[Date]
<= '850515') ORDERED FORWARD)|||Hamed,
Please, can you post indexes for each table also?
Seems that column [dbo].[tblInvRate].[IsRated] is not integer, correct?.
Ttry casting the value you compare with. Let us assume it is bit data type,
then:
|--Filter(WHERE:(Convert([b].[IsRated])=1))
(
SELECT TOP 1
Rate
FROM
dbo.tblInvRate
WHERE
fkPart = dbo.tblPart.pkCode
AND [Date]<='850515'
And IsRated = cast(1 as bit) <-- change this
)
AMB
"Hamed" wrote:
> This is yours:
> |--Compute Scalar(DEFINE:([b].[Rate]=[b].[Rate]))
> |--Nested Loops(Left Outer Join, OUTER
> REFERENCES:([tblPart].[pkCode]))
> |--Filter(WHERE:([Expr1003]>0))
> | |--Merge Join(Inner Join,
> MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
> RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
> | |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
> DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
> | | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
> WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
> | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
> |--Top(1)
> |--Filter(WHERE:(Convert([b].[IsRated])=1))
> |--Bookmark Lookup(BOOKMARK:([Bmk1004]),
> OBJECT:([PosKish].[dbo].[tblInvRate] AS [b]) WITH PREFETCH)
> |--Index
> Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [b]),
> SEEK:([b].[fkPart]=[tblPart].[pkCode] AND [b].[Date] <= '850515')
> ORDERED FORWARD)
> and this is mine:
> |--Compute Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
> |--Nested Loops(Left Outer Join, OUTER
> REFERENCES:([tblPart].[pkCode]))
> |--Filter(WHERE:([Expr1003]>0))
> | |--Merge Join(Inner Join,
> MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
> RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
> | |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
> DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
> | | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
> WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
> | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
> |--Compute
> Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
> |--Sort(TOP 1, ORDER BY:([tblInvRate].[Date] DESC,
> [tblInvRate].[Time] DESC))
> |--Filter(WHERE:(Convert([tblInvRate].[IsRated])=1))
> |--Bookmark Lookup(BOOKMARK:([Bmk1004]),
> OBJECT:([PosKish].[dbo].[tblInvRate]) WITH PREFETCH)
> |--Index
> Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11]),
> SEEK:([tblInvRate].[fkPart]=[tblPart].[pkCode] AND [tblInvRate].[Date]
> <= '850515') ORDERED FORWARD)
>|||Hamed,
In my first post I forgot the "order by" clause in the correlated subquery.
I think that is the reason why we are getting diffrent execution plans.
select
a.*,
(
SELECT TOP 1
b.Rate
FROM
dbo.tblInvRate as b
WHERE
b.fkPart = a.pkCode
AND b.[Date] <= '850515'
And b.IsRated = 1
ORDER BY [Date] DESC, [Time] DESC <-- forgot this
) as Rate
from
(
SELECT
dbo.tblPart.pkCode,
dbo.tblPart.Title,
SUM(Rate.Qty * Rate.Type) AS PriorRemain
FROM
dbo.tblInvRate as Rate
INNER JOIN
dbo.tblPart
ON Rate.fkPart = dbo.tblPart.pkCode
WHERE
Rate.[Date] < '850515'
GROUP BY
dbo.tblPart.pkCode, dbo.tblPart.Title
HAVING
SUM(Rate.Qty * Rate.Type)>0
) as a
go
AMB
"Hamed" wrote:
> This is yours:
> |--Compute Scalar(DEFINE:([b].[Rate]=[b].[Rate]))
> |--Nested Loops(Left Outer Join, OUTER
> REFERENCES:([tblPart].[pkCode]))
> |--Filter(WHERE:([Expr1003]>0))
> | |--Merge Join(Inner Join,
> MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
> RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
> | |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
> DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
> | | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
> WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
> | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
> |--Top(1)
> |--Filter(WHERE:(Convert([b].[IsRated])=1))
> |--Bookmark Lookup(BOOKMARK:([Bmk1004]),
> OBJECT:([PosKish].[dbo].[tblInvRate] AS [b]) WITH PREFETCH)
> |--Index
> Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [b]),
> SEEK:([b].[fkPart]=[tblPart].[pkCode] AND [b].[Date] <= '850515')
> ORDERED FORWARD)
> and this is mine:
> |--Compute Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
> |--Nested Loops(Left Outer Join, OUTER
> REFERENCES:([tblPart].[pkCode]))
> |--Filter(WHERE:([Expr1003]>0))
> | |--Merge Join(Inner Join,
> MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
> RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
> | |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
> DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
> | | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
> WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
> | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
> |--Compute
> Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
> |--Sort(TOP 1, ORDER BY:([tblInvRate].[Date] DESC,
> [tblInvRate].[Time] DESC))
> |--Filter(WHERE:(Convert([tblInvRate].[IsRated])=1))
> |--Bookmark Lookup(BOOKMARK:([Bmk1004]),
> OBJECT:([PosKish].[dbo].[tblInvRate]) WITH PREFETCH)
> |--Index
> Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11]),
> SEEK:([tblInvRate].[fkPart]=[tblPart].[pkCode] AND [tblInvRate].[Date]
> <= '850515') ORDERED FORWARD)
>

Friday, March 30, 2012

How to insert the date

How to insert resultset from SP into a table? or use Select * From

How to Insert Raw text into SQL table

how to insert picture into a table

Wednesday, March 28, 2012

how to insert back salesh into table using query?

Monday, March 26, 2012

How to insert a space after each Manager Starts.

How to insert a new record (and get it's identity), if a record is not found

Friday, March 23, 2012

How to increase timeout?

How to increase timeout?

How to increase timeout?

How to include total in query?

how to include the nulls?

Wednesday, March 21, 2012

How to include a single quote in a sql query

How to improve this SQL statement?

How to improve this query?

how to improve this query, thanks

How to improve the query performance on cubes?

How to improve the query performance for my reports?

How to improve the query performance for my reports?

How to improve the performance,

hide system tables

Blog Archive

About Me