I want to split the records from table1 and insert the splited records to table2. The trigger is working fine when i am trying to update single record. but the trigger is not working properly at the time of batch update. At the time of batch update it is split the last record from the query and insert that into table2 instead of inserting all the records.
Pls help me........I suspect you wrote your trigger to work with one record at a time. I say this because of your comment of the trigger working for a one record update and the LAST record in a batch. Take a look at your trigger and ask yourself "What happens if I have three records to deal with in either the inserted OR deleted temp tables?"
Post your trigger and maybe we can offer some suggestions.|||Hi
This is the trigger i have used to split the records for various conditions from table1 and insert the splitted records into another table table2
The Problem i'm facing is if it is single update(if only one row is affected) the trigger is working fine,
but not for batch update.
if you are unable to follow this trigger pls give your own batch update example, we will try to implement that.
pls help me.
CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid
*/
AFTER UPDATE
AS
Declare @.Id int
Declare @.Res_id nvarchar(8)
Declare @.DTime smalldatetime
Declare @.StartTime char(5)
Declare @.EndTime char(5)
Declare @.Pauze char(5)
Declare @.Hourworked nvarchar(5)
Declare @.Status char(1)
--Declare @.PPayroll bit
Declare @.Project nvarchar(10)
Declare @.Naam char(50)
Declare @.Type char(5)
Declare @.Hryear nvarchar(4)
Declare @.Totmin char(5)
Declare @.Endtimebr char(5)
Declare @.Stime char(5)
Declare @.Start char(5)
Declare @.Processed char(1)
Declare @.del_DTime smalldatetime
Declare @.del_StartTime char(5)
Declare @.del_EndTime char(5)
Declare @.Del_id int
Declare @.Totrows int
--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id
Select @.Id=Id,
@.Res_id=Res_id,
@.DTime=[Datetime],
@.Start=Starttime,
@.EndTime=Endtime,
@.Pauze=Pauze,
@.Hourworked=Hoursworked,
@.Status=Status,
@.Project=Project,
@.Naam=Naam,
@.Type=Type,
@.Hryear=Hryear,
@.Processed=Processed
From inserted -- i ,Hours_tmp h where i.[Id] = h.[id]
Select @.del_DTime=[DateTime],
@.del_Starttime=Starttime,
@.del_endTime=Endtime ,
@.del_id=id
From Deleted
Delete From Selnmuamid
Where Res_id=@.Res_id
and Datetime=@.del_DTime
and Starttime >=@.del_starttime
and Endtime <=@.del_endtime
-- This is To Get total hours (hourworked column in the table ) in minutes
Select @.Totmin=Sum(Convert(Integer,(Left(@.hourworked,Char index('.',@.hourworked)-1) *
60)) +Convert(Integer,(Right(@.hourworked,Len(@.hourworke d) -
Charindex('.',@.hourworked))))),@.Endtimebr=Sum(Conv ert(Integer,(Left(@.Endtime,Charindex(':',@.Endtime) -1) *
60)) + Convert(Integer,(Right(@.Endtime,Len(@.Endtime) -
Charindex(':',@.Endtime))))),@.Stime=Sum(Convert(Int eger,(Left(@.Start,Charindex(':',@.Start)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))
--Values Passed as Parameter to Stored procedure Break_Split
Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,
@.Hourworked,@.Status,@.Project,@.Naam,@.Type,
@.Hryear,@.Totmin,@.Endtimebr,@.STime,@.Processed|||basically I just wrapped your code in a while loop to step through each record in the inserted temp table. Then I moveded the delete Selnmuamid to the end.
This has NOT been tested!
Code:
------------------------------
CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid
*/
AFTER UPDATE
AS
-- ----------------
-- One Declare is more efficient than multipule declares
-- ----------------
Declare @.Id int
, @.Res_id nvarchar(8)
, @.DTime smalldatetime
, @.StartTime char(5)
, @.EndTime char(5)
, @.Pauze char(5)
, @.Hourworked nvarchar(5)
, @.Status char(1)
-- , @.PPayroll bit
, @.Project nvarchar(10)
, @.Naam char(50)
, @.Type char(5)
, @.Hryear nvarchar(4)
, @.Totmin char(5)
, @.Endtimebr char(5)
, @.Stime char(5)
, @.Start char(5)
, @.Processed char(1)
, @.del_DTime smalldatetime
, @.del_StartTime char(5)
, @.del_EndTime char(5)
, @.Del_id int
, @.Totrows int
--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id
select @.Id = min(ID) from inserted
while (@.Id is not null) begin
Select @.Res_id = Res_id
, @.DTime = [Datetime]
, @.Start = Starttime
, @.EndTime = Endtime
, @.Pauze = Pauze
, @.Hourworked = Hoursworked
, @.Status = Status
, @.Project = Project
, @.Naam = Naam
, @.Type = Type
, @.Hryear = Hryear
, @.Processed = Processed
-- ---------------------------------------------------
-- Hoursworked, Endtime and StartTime need to be converted to minutes
-- ---------------------------------------------------
, @.Totmin = Convert(Integer,(Left(Hoursworked,Charindex('.',Ho ursworked)-1) * 60)) + Convert(Integer,(Right(Hoursworked,Len(Hoursworked ) - Charindex('. ',Hoursworked))))
, @.Endtimebr = Convert(Integer,(Left(Endtime, Charindex(':',Endtime)-1) * 60)) + Convert(Integer,(Right(Endtime, Len(Endtime) - Charindex(':',Endtime))))
, @.Stime = Convert(Integer,(Left(Starttime, Charindex(':',Starttime)-1) * 60)) + Convert(Integer,(Right(Starttime, Len(Starttime) - Charindex(':',Starttime)))))
From inserted i
where id = @.id
-- This is To Get total hours (hourworked column in the table ) in minutes
/*
Select @.Totmin = Sum(Convert(Integer,(Left(@.hourworked,Charindex('. ',@.hourworked)-1) * 60)) +
Convert(Integer,(Right(@.hourworked,Len(@.hourworked ) - Charindex('. ',@.hourworked)))))
, @.Endtimebr = Sum(Convert(Integer,(Left(@.Endtime,Charindex(':',@. Endtime)-1) * 60)) +
Convert(Integer,(Right(@.Endtime,Len(@.Endtime) - Charindex(':',@.Endtime)))))
, @.Stime = Sum(Convert(Integer,(Left(@.Start,Charindex(':',@.St art)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))
*/
-- Values Passed as Parameter to Stored procedure Break_Split
Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,@.Hourwor ked,@.Status,@.Project,@.Naam,@.Type,@.Hryear,@.Totmin,@. Endtimebr,@.STime,@.Processed
select @.Id = min(ID) from inserted where id > @.id
end
-- -------------------
-- The deleted temp table can be joined to the Selnmuamid table
-- to deleted the unwanted records. If you need the ID from the
-- inserted table then join the inserted table with the
-- deleted table and then join the Selnmuamid table.
-- This is a good example of how you can ditch the row-at-a-time
-- mentality and start using set theory for processing records.
-- -------------------
delete
from Selnmuamid s
join deleted d on s.Res_id = d.Res_id
where [s.Datetime] = d.del_DTime
and s.Starttime >= d.del_starttime
and s.Endtime <= d.del_endtime
/*
Select @.del_DTime = [DateTime]
, @.del_Starttime = Starttime
, @.del_endTime = Endtime
, @.del_id = [id]
From Deleted
where Id = @.Id
Delete
From Selnmuamid
Where Res_id = @.Res_id
and [Datetime] = @.del_DTime
and Starttime >= @.del_starttime
and Endtime <= @.del_endtime
*/
------------------------------
Showing posts with label working. Show all posts
Showing posts with label working. Show all posts
Friday, March 30, 2012
How to insert NULL char values in SQLSERVER with a SQL sentence?
Hi everyone!
I am working with Delphi v7 and MS SQLServer.
I am trying to insert data in a table with a SQL sentence. Some of the
fields of my table are type char or varchar, and they can have null
values.
What do i have to write in the SQL sentence to insert a null value in
those fields?
I tried with '', an empty String, but it doesnt work, the tables
stores an empty String (logical :-)).
In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
value, but how can i tell this to the SQLServer through a SQL
Sentence?
I am working with Delphi v7 and MS SQLServer.
I am trying to insert data in a table with a SQL sentence. Some of the
fields of my table are type char or varchar, and they can have null
values.
What do i have to write in the SQL sentence to insert a null value in
those fields?
I tried with '', an empty String, but it doesnt work, the tables
stores an empty String (logical :-)).
In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
value, but how can i tell this to the SQLServer through a SQL
Sentence?
Well, thank you very much.On 29 Jul 2004 04:15:15 -0700, schumacker wrote:
>Hi everyone!
>I am working with Delphi v7 and MS SQLServer.
>I am trying to insert data in a table with a SQL sentence. Some of the
>fields of my table are type char or varchar, and they can have null
>values.
>What do i have to write in the SQL sentence to insert a null value in
>those fields?
>I tried with '', an empty String, but it doesnt work, the tables
>stores an empty String (logical :-)).
>In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
>value, but how can i tell this to the SQLServer through a SQL
>Sentence?
>Well, thank you very much.
Hi schumacker,
INSERT INTO MyTable (Col1, Col2, Col3)
VALUES (1, NULL, 3)
or
INSERT INTO MyTable (Col1, Col2, Col3)
SELECT 1, NULL, 3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Given:
create table foo
(col1 char(1) not null,
col2 char(1) null)
you can insert nulls to col2 by either explicitly specifying a null for the
content:
insert foo (col1, col2) values ('a',null)
or by skipping it in the column list and SQL Server will automatically
insert null:
insert foo (col1) values ('b')
Check it:
select * from foo
outputs:
col1 col2
-- --
a NULL
b NULL
"schumacker" <miguelcampoy@.hotmail.com> wrote in message
news:e1eadaf3.0407290315.99cd0d2@.posting.google.co m...
> Hi everyone!
> I am working with Delphi v7 and MS SQLServer.
> I am trying to insert data in a table with a SQL sentence. Some of the
> fields of my table are type char or varchar, and they can have null
> values.
> What do i have to write in the SQL sentence to insert a null value in
> those fields?
> I tried with '', an empty String, but it doesnt work, the tables
> stores an empty String (logical :-)).
> In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
> value, but how can i tell this to the SQLServer through a SQL
> Sentence?
> Well, thank you very much.
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.
i am waiting.
bye
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
Wednesday, March 21, 2012
How to include SQL Express in Installation Package and change some option?
Hi, there,
I'm working on a windows application project which uses SQL Express as
database.
I found I can include SQL Server 2005 Express Edition in the installation
project as Prerequests,
that's cool.
But I need do more things, I want to change the "Authentication Mode" from
"Windows Authentication mode"
(which is default) to "SQL Server Authentication mode" and assign a "default
password" to user "sa", and
I want to attach a database file which will be include on the installation
disc.
So what shall I do? Write some script in the installation project?
Does SQL Express provide any program interface to do that?
It seems there is no help content for this in the MSDN and I did some search
job and got nothing.
Help needed. Thank you.
Best wishes!
I could write a chapter on this but here it is in a nutshell.
You need to use the Unattended Install tenplate.ini file along with the
LOGName and Datasource.xml.
Here is a fantastic article.
http://www.devx.com/dbzone/Article/31648
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi, there,
> I'm working on a windows application project which uses SQL Express as
> database.
> I found I can include SQL Server 2005 Express Edition in the installation
> project as Prerequests,
> that's cool.
> But I need do more things, I want to change the "Authentication Mode" from
> "Windows Authentication mode"
> (which is default) to "SQL Server Authentication mode" and assign a
> "default password" to user "sa", and
> I want to attach a database file which will be include on the installation
> disc.
> So what shall I do? Write some script in the installation project?
> Does SQL Express provide any program interface to do that?
> It seems there is no help content for this in the MSDN and I did some
> search job and got nothing.
> Help needed. Thank you.
> Best wishes!
>
|||Hmm.....
Thank you.
I have read it, and there is really something interesting.
But there is another thing I can not do with this article.
I want to open Name Pipe and TCP connection of the SQL EXPRESS during the
setup,
but there is no such setting in the template.ini file.
Any other help?
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uFytSE%235GHA.3592@.TK2MSFTNGP05.phx.gbl...
>I could write a chapter on this but here it is in a nutshell.
> You need to use the Unattended Install tenplate.ini file along with the
> LOGName and Datasource.xml.
> Here is a fantastic article.
> http://www.devx.com/dbzone/Article/31648
>
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
> news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
>
|||Philip,
All you need to do is edit the package.xml file - you can specify the
command line parameters you want to use for installing SQLExpress. The file
is located in
C:\Program Files\Microsoft Visual Studio
8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
The default setup is
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1'
You might want to change it to something like
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
DISABLENETWORKPROTOCOLS=0'
See the readme file for SQLExpress for the command line options. That will
get SQLExpress installed the way you want it. Use a custom action dll to
install your db, attach it, set up the user accounts you want, etc.
Russ Stevens
|||Ah haaaaaa!
This is right the solution I am looking for!
Powerful!
Thank you very much!
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:OkMqQxy8GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Philip,
> All you need to do is edit the package.xml file - you can specify the
> command line parameters you want to use for installing SQLExpress. The
> file is located in
> C:\Program Files\Microsoft Visual Studio
> 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
> The default setup is
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1'
> You might want to change it to something like
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
> DISABLENETWORKPROTOCOLS=0'
> See the readme file for SQLExpress for the command line options. That will
> get SQLExpress installed the way you want it. Use a custom action dll to
> install your db, attach it, set up the user accounts you want, etc.
> Russ Stevens
>
I'm working on a windows application project which uses SQL Express as
database.
I found I can include SQL Server 2005 Express Edition in the installation
project as Prerequests,
that's cool.
But I need do more things, I want to change the "Authentication Mode" from
"Windows Authentication mode"
(which is default) to "SQL Server Authentication mode" and assign a "default
password" to user "sa", and
I want to attach a database file which will be include on the installation
disc.
So what shall I do? Write some script in the installation project?
Does SQL Express provide any program interface to do that?
It seems there is no help content for this in the MSDN and I did some search
job and got nothing.
Help needed. Thank you.
Best wishes!
I could write a chapter on this but here it is in a nutshell.
You need to use the Unattended Install tenplate.ini file along with the
LOGName and Datasource.xml.
Here is a fantastic article.
http://www.devx.com/dbzone/Article/31648
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi, there,
> I'm working on a windows application project which uses SQL Express as
> database.
> I found I can include SQL Server 2005 Express Edition in the installation
> project as Prerequests,
> that's cool.
> But I need do more things, I want to change the "Authentication Mode" from
> "Windows Authentication mode"
> (which is default) to "SQL Server Authentication mode" and assign a
> "default password" to user "sa", and
> I want to attach a database file which will be include on the installation
> disc.
> So what shall I do? Write some script in the installation project?
> Does SQL Express provide any program interface to do that?
> It seems there is no help content for this in the MSDN and I did some
> search job and got nothing.
> Help needed. Thank you.
> Best wishes!
>
|||Hmm.....
Thank you.
I have read it, and there is really something interesting.
But there is another thing I can not do with this article.
I want to open Name Pipe and TCP connection of the SQL EXPRESS during the
setup,
but there is no such setting in the template.ini file.
Any other help?
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uFytSE%235GHA.3592@.TK2MSFTNGP05.phx.gbl...
>I could write a chapter on this but here it is in a nutshell.
> You need to use the Unattended Install tenplate.ini file along with the
> LOGName and Datasource.xml.
> Here is a fantastic article.
> http://www.devx.com/dbzone/Article/31648
>
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
> news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
>
|||Philip,
All you need to do is edit the package.xml file - you can specify the
command line parameters you want to use for installing SQLExpress. The file
is located in
C:\Program Files\Microsoft Visual Studio
8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
The default setup is
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1'
You might want to change it to something like
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
DISABLENETWORKPROTOCOLS=0'
See the readme file for SQLExpress for the command line options. That will
get SQLExpress installed the way you want it. Use a custom action dll to
install your db, attach it, set up the user accounts you want, etc.
Russ Stevens
|||Ah haaaaaa!
This is right the solution I am looking for!
Powerful!
Thank you very much!
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:OkMqQxy8GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Philip,
> All you need to do is edit the package.xml file - you can specify the
> command line parameters you want to use for installing SQLExpress. The
> file is located in
> C:\Program Files\Microsoft Visual Studio
> 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
> The default setup is
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1'
> You might want to change it to something like
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
> DISABLENETWORKPROTOCOLS=0'
> See the readme file for SQLExpress for the command line options. That will
> get SQLExpress installed the way you want it. Use a custom action dll to
> install your db, attach it, set up the user accounts you want, etc.
> Russ Stevens
>
How to include data from 2 unlinked tables
I am using VB6 and CR10 & SQL Server 7
I am working on an inventory project nw.. I'm new to crystal reports..
For the report i need to pick data from 2 tables(Sales_Details, Purchase_Details)
There is no link between these 2 tables (except a similarity that both have got a date field)
TABLE 1
Sale_Date... SalesQty
01/01/07...... 50
01/03/07...... 24
01/04/07...... 10
TABLE 2
Purch_Date... Purchase Qty
01/01/07 ....... 100
01/06/07 ....... 100
I need to take a report which shows the movement of a particular Item based on date range
It should look like this
------x----x----x
Date ......... Sales.......Purchase
------x----x----x
01/01/07 ........ 50 ........ 100
01/03/07 ........ 24 ........ 0
01/04/07 ........ 10 ........ 0
01/06/07 ........ 0 ........ 100
--------------
I tried inner and outer joins to links these tables, but all in vein( data is getting repeated then), tried with groups also..no use
Pleeeaaase... help me to do this...
This may be a simple issue for most of you...
But i am a beginner.. i need your valuable help..
Plzzzzz...Just to make sure, but there are no PKeys in either one of your tables?|||You say you need to report by item, so surely there must be some sort of item code in both tables otherwise how do you know what item you're buying / selling?|||Ofcourse item code is there... For sake of simplicity i dint mention that.
Actually both the purchase and sales table has the fileds- Date, Itemcode, Quantity
Sales Table
Date...... ItemCode........Qnty
------------
01/01/07 ..A001............50
01/03/07...A001............24
01/04/07...A001............10
Purchase Table
Date...... ItemCode........Qnty
------------
01/01/07...A001............100
01/06/07...A001............100
and i need a report (like the one mentioned above)based on the movement of Item -A001 ..
Please...........|||First:
You wrote:
There is no link between these 2 tables (except a similarity that both have got a date field)
This is the reason for the questions now I assume itemcode is your link.
Next you will group by date and itemcode if you want, now for grouping by date go to options and choose section to be printed by day.
Then you will need to create formulas that will check to see the quantity of sales and purchases.
Something like:
If {Sales.quantity} > 0
Then {Sales.quantity}
Else 0
Hope that helps,
GJ
I am working on an inventory project nw.. I'm new to crystal reports..
For the report i need to pick data from 2 tables(Sales_Details, Purchase_Details)
There is no link between these 2 tables (except a similarity that both have got a date field)
TABLE 1
Sale_Date... SalesQty
01/01/07...... 50
01/03/07...... 24
01/04/07...... 10
TABLE 2
Purch_Date... Purchase Qty
01/01/07 ....... 100
01/06/07 ....... 100
I need to take a report which shows the movement of a particular Item based on date range
It should look like this
------x----x----x
Date ......... Sales.......Purchase
------x----x----x
01/01/07 ........ 50 ........ 100
01/03/07 ........ 24 ........ 0
01/04/07 ........ 10 ........ 0
01/06/07 ........ 0 ........ 100
--------------
I tried inner and outer joins to links these tables, but all in vein( data is getting repeated then), tried with groups also..no use
Pleeeaaase... help me to do this...
This may be a simple issue for most of you...
But i am a beginner.. i need your valuable help..
Plzzzzz...Just to make sure, but there are no PKeys in either one of your tables?|||You say you need to report by item, so surely there must be some sort of item code in both tables otherwise how do you know what item you're buying / selling?|||Ofcourse item code is there... For sake of simplicity i dint mention that.
Actually both the purchase and sales table has the fileds- Date, Itemcode, Quantity
Sales Table
Date...... ItemCode........Qnty
------------
01/01/07 ..A001............50
01/03/07...A001............24
01/04/07...A001............10
Purchase Table
Date...... ItemCode........Qnty
------------
01/01/07...A001............100
01/06/07...A001............100
and i need a report (like the one mentioned above)based on the movement of Item -A001 ..
Please...........|||First:
You wrote:
There is no link between these 2 tables (except a similarity that both have got a date field)
This is the reason for the questions now I assume itemcode is your link.
Next you will group by date and itemcode if you want, now for grouping by date go to options and choose section to be printed by day.
Then you will need to create formulas that will check to see the quantity of sales and purchases.
Something like:
If {Sales.quantity} > 0
Then {Sales.quantity}
Else 0
Hope that helps,
GJ
How to improve performance if inner join has more than 2 or 3 tables
Hi everyone
I need a solution for this query. It is working fine for 2 tables but when there are 1000's of records in each table and query has more than 2 tables. The process never ends.
Here is the query
(select siqPid= 1007, t1.Gmt909Time as GmtTime,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as EngValue,
t1.Loc1Time as locTime,t1.msgId
into #temp5
from #temp1 as t1,#temp2 as t2,#temp3 as t3,#temp4 as t4
where t1.Loc1Time = t2.Loc1Time and t2.Loc1Time = t3.Loc1Time and t3.Loc1Time = t4.Loc1Time)
(select siqPid= 1007, t1.Gmt909Time as GmtTime,
t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
--into #temp5
from #temp1 as t1
where exists
(Select 1
from #temp2 as t2
where t1.Loc1Time = t2.Loc1Time and
exists
(Select 1
from #temp3 as t3
where t2.Loc1Time = t3.Loc1Time and
exists
(Select 1
from #temp4 as t4
where t3.Loc1Time = t4.Loc1Time))))
What do you mean by the engValues can't be added? You shouldn't have problems joining 4 tables with 1000's of records. SQL Server is designed for queries involving orders of magnitude more records. Here are some suggestions:
1. Use better join syntax. Instead of FROM A, B WHERE A.ID = B.ID use FROM A INNER JOIN B ON A.ID = B.ID.
2. Read up on Table Variables and use them instead of your temp tables. Here's a good link to read about them:http://odetocode.com/Articles/365.aspx. In particular, note that you can define primary keys for table variables which should assist in your query performance.
HTH
|||Thanks ZLA for the input
The problem is there is already alot of work done before and temp tables are crreated already. So I need to use those for this project.
Can you please help me in refining the query. I did use the syntax you asked me, but its still taking too long. Something like this
Select t1.engValue+t2.engValue+t3.engValue+t4.engValue
from #temp5 t5
inner join #temp1 t1
on t1.Loc1Time = t5.locTime
inner join #temp2 t2
on t2.Loc1Time = t5.locTime
inner join #temp3 t3
on t3.Loc1Time = t5.locTime
inner join #temp4 t4
on t4.Loc1Time = t5.locTime
but that too is taking too long... Please help me with this..
|||Have you looked at the estimated or actual execution plans for your query. In general, table scans perform slow and I suspect you have too many of them in your execution plan. It is usually better if the plan makes use of indexes.
You can create temp tables with identities, primary keys and indexes. Here is a good article that shows how:http://www.sqlteam.com/item.asp?ItemID=15442 Even though you are stuck with the temp tables, can you at least add some indexes to them to speed up the query execution?
There are other alternatives as well; you might try thinking laterally. Perhaps you can update temp table 5 (or use a separate temp table 6) where you insert 1 row from each of the other 4 temp tables. Just insert locTime, engValue. Then do the following:
SELECT locTime, Sum(engValue) From #Temp6 GROUP BY locTime HAVING Count(*) = 4
This gives the sum but only where all four tables have inserted a record (equivalent to locTime being present in all four temp tables which your inner joins select for).
HTH
|||Hi ZLA
Thats article has very good points, I can incorporate that in my process but I need to get rid of the error also in this query. Can you please help me in finding out why am I getting an error on this line and what can I do to make it work.
,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
(select siqPid= 1007, t1.Gmt909Time as GmtTime,
t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
--into #temp5
from #temp1 as t1
where exists
(Select 1
from #temp2 as t2
where t1.Loc1Time = t2.Loc1Time and
exists
(Select 1
from #temp3 as t3
where t2.Loc1Time = t3.Loc1Time and
exists
(Select 1
from #temp4 as t4
where t3.Loc1Time = t4.Loc1Time))))
You suggestions are awasome, and the last suggestion that you gave did actually worked for me, but I still want to know why the where exisit query doesnt work, if I want to add the column values from the later tables.
I would appreciate your help on this.
-Sarah
|||Is it in this last query you are still getting an error? What error are you getting? Even without that, I suspect the problem is because not all the fields exist. Using the exists clause, your query is essentially:
select siqPid= 1007, t1.Gmt909Time as GmtTime, t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
from #temp1 as t1
where exists ( ... )
This select statement only pulls selected fields from #temp1. Therefore t2, t3, t4 aren't available. They are only used in the WHERE clause. You'll need to go back to a join or use my prior idea of inserting their individual results into a new temp table (#temp6). Then sum across temp6 into temp5.
|||
How long is it taking?
How long is "too long"?
How many rows does each of the temp tables have?
|||Yes, the inner join is actually never shows up any results, it says its processing the query... I ran it with 282 records each in 4 tables, and the process never got completed, so talking about 1000 of records in each tables it far. But I will be doing that eventually, so I dont know whats wrong with the inner join statement too.
ZLA, yes, that makes sense, the exists clause probably is doing that. And the sum does work with teh group by statement. So for right now I have a solution that you suggested to sum them up. For one time its fine, but not always... as I had to put extra three query's instead of putting just one inner join query.
But to avoid future errors, is the where clause or on clause in the inner join query asking for too much, 'cause there is no clear filter like loctime = 5/6/1990...
Please let me know how else can I improve my inner join then..
Thanks
-Sarah
|||Am sorry, I was talking about this query now in my last post
Select t1.engValue+t2.engValue+t3.engValue+t4.engValue
from #temp5 t5
inner join #temp1 t1
on t1.Loc1Time = t5.locTime
inner join #temp2 t2
on t2.Loc1Time = t5.locTime
inner join #temp3 t3
on t3.Loc1Time = t5.locTime
inner join #temp4 t4
on t4.Loc1Time = t5.locTime
|||
I started to try to test this locally by creating my own test tables and realized one possible cause for your query to take so long, even with so few records. How many duplicate locTime / Loc1Time values are there across the 5 temp tables?
For example, assume the same datetime value appears 10 times in each table. Since you are only joining on the datetime field, temp5 joined to temp1 will produce 10 x 10 = 100 result rows. Since you have 5 tables, 10 duplicates could produce 100,000 rows by themselves. If your actual number of duplicates is higher than my example, you could be trying to run a query that will return a million or more records.
Try running the following to test this:
SELECT locTime, Count(*) FROM #temp5 GROUP BY locTime HAVING Count(*) > 1 ORDER BY Count(*) DESC
SELECT loc1Time, Count(*) FROM #temp1 GROUP BY loc1Time HAVING Count(*) > 1 ORDER BY Count(*) DESC
etc...
Let me know what you find out.
|||Well, I went ahead and tested this locally. For my test, I was able to join the tables, each with only 3 duplicates, to return 74,500 rows in less than a second. Below is all of the SQL I used to test this. If you are still seeing the performance problems, it makes me think either the server is way overloaded or there is some other resource constraint impeding your efforts.
Test Table Creation:
ifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T5]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T5]GOifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T1]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T1]GOifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T2]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T2]GOifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T3]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T3]GOifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T4]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T4]GOCREATE TABLE [dbo].[T5] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[locTime] [datetime]NOT NULL )ON [PRIMARY]GOCREATE TABLE [dbo].[T1] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[loc1Time] [datetime]NOT NULL ,[engValue] [float]NOT NULL )ON [PRIMARY]GOCREATE TABLE [dbo].[T2] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[loc1Time] [datetime]NOT NULL ,[engValue] [float]NOT NULL )ON [PRIMARY]GOCREATE TABLE [dbo].[T3] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[loc1Time] [datetime]NOT NULL ,[engValue] [float]NOT NULL )ON [PRIMARY]GOCREATE TABLE [dbo].[T4] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[loc1Time] [datetime]NOT NULL ,[engValue] [float]NOT NULL )ON [PRIMARY]GOALTER TABLE [dbo].[T5]WITH NOCHECK ADD CONSTRAINT [PK_T5]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GOALTER TABLE [dbo].[T1]WITH NOCHECK ADD CONSTRAINT [PK_T1]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GOALTER TABLE [dbo].[T2]WITH NOCHECK ADD CONSTRAINT [PK_T2]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GOALTER TABLE [dbo].[T3]WITH NOCHECK ADD CONSTRAINT [PK_T3]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GOALTER TABLE [dbo].[T4]WITH NOCHECK ADD CONSTRAINT [PK_T4]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GO
Test Table Population:
DECLARE @.Counterint, @.Datedatetime, @.Table intSET @.Counter = 0SET @.Date ='01/01/2001 00:00'WHILE (@.Counter < 1000)BEGIN IF @.Counter = 350or @.Counter = 700BEGIN SET @.Date ='01/01/2001 00:00'END INSERT T5 (locTime)VALUES (@.Date)SET @.Counter = @.Counter + 1SET @.Date =DateAdd(hh, 1, @.Date)ENDSET @.Table = 1WHILE (@.Table < 5)BEGIN SET @.Counter = 0SET @.Date ='01/01/2001 00:00'WHILE (@.Counter < 1000)BEGIN IF @.Counter = 350or @.Counter = 700BEGIN SET @.Date ='01/01/2001 00:00'END IF @.Table = 1INSERT T1 (loc1Time, engValue)VALUES (@.Date, rand())ELSE IF @.Table = 2INSERT T2 (loc1Time, engValue)VALUES (@.Date, rand())ELSE IF @.Table = 3INSERT T3 (loc1Time, engValue)VALUES (@.Date, rand())ELSE IF @.Table = 4INSERT T4 (loc1Time, engValue)VALUES (@.Date, rand())SET @.Counter = @.Counter + 1SET @.Date =DateAdd(hh, 1, @.Date)END SET @.Table = @.Table + 1ENDselectcount(*)from T5selectcount(*)from T1selectcount(*)from T2selectcount(*)from T3selectcount(*)from T4
Temp Table Join Test:
SELECT *INTO #temp5FROM T5SELECT *INTO #temp1FROM T1SELECT *INTO #temp2FROM T2SELECT *INTO #temp3FROM T3SELECT *INTO #temp4FROM T4Select t1.engValue+t2.engValue+t3.engValue+t4.engValuefrom #temp5 t5innerjoin #temp1 t1on t1.Loc1Time = t5.locTimeinnerjoin #temp2 t2on t2.Loc1Time = t5.locTimeinnerjoin #temp3 t3on t3.Loc1Time = t5.locTimeinnerjoin #temp4 t4on t4.Loc1Time = t5.locTimeDROP TABLE #temp5DROP TABLE #temp1DROP TABLE #temp2DROP TABLE #temp3DROP TABLE #temp4|||
Hi ZLA
Yes, that can be a possiblity because for certain locValues, I have been having probably 20-30 duplicate locId's that must be the cause of keeping the process busy.
I still need to go over the table example you showed me, I will go over it tomorrow, its been very busy at work today... I am really thankful to you for this help.
Tomorrow I will go over it and will create more duplicate records and will confirm if thats the reason of my problem.
Thanks once again
-Sarah
|||
That sounds like it's the cause, then. If you have trouble figuring out how to handle the duplicates, let me know. You'll need to decide / know what the correct answer should be. For example, if
Temp1.loc1Time = '7/1/2006 12:34', Temp1.engValue = 1.0
Temp1.loc1Time = '7/1/2006 12:34', Temp1.engValue = 2.0
Temp2.loc1Time = '7/1/2006 12:34', Temp2.engValue = 0.1
Temp2.loc1Time = '7/1/2006 12:34', Temp2.engValue = 0.2
Assume all other matching records have engValue of 0.0. Using joins, you would get 4 matches: 1.0 and 0.1, 1.0 and 0.2, 2.0 and 0.1, 2.0 and 0.2. That would give you 4 separate sums for that time: 1.1, 1.2, 2.1 and 2.2. If those four are summed together, the grand total is 6.6.
If, however, the grand total for that time should just be 3.3, then the joins are not the right method.
Let me know what you find out.
|||Hi ZLA
Yes, thats exactly whats happening, one table has 100 adn another has 98 rows with loc1time as 1/1/1900. and with teh join, when I stop in a matter of seconds its showing 14,000 rows and its still not complete.
Yes, it seems like its giving the result of 6.6 and we want 3.3. Either we need to group by the loc1time in each table adn make sure that there is one record in each of the temp tables or delete teh enteries from teh temp tables that have duplicate joining condition.
I think we are going towards your first solution, which asked for grouping in the fifth table with the expression and summing the values up.
what do you suggest?
-Sarah
|||Hurrah! I can think of several approaches:
1. Is the 1/1/1900 date valid? If not, either correct the underlying problem or exclude these records from the query. To exclude them, you could eliminate them when creating #temp1, #temp2, ... Or you could add a general where clause to the multiple inner join such as "WHERE temp1.loc1Time <> '1/1/1900' AND temp2loc1Time <> '1/1/1900' ..." Or you could modify the joins to do the filtering: "FROM #temp5 t5 INNER JOIN #temp1 t1 ON t1.Loc1Time = t5.locTime AND t1.Loc1Time <> '1/1/1900' ..." I think the first is best if possible; the last is next best.
2. If duplicate values are not correct, I would recommend building the temp tables so they only hold the distinct values you want. That should be the most efficient. If possible, you could get that by adding DISTINCT to the select statement that builds them. If you can't alter the temp table creation, you can use sub-queries in your join:
Select t1.engValue+t2.engValue+t3.engValue+t4.engValue
from (select distinct locTime from #temp5) t5
innerjoin (select distinct Loc1Time from #temp1) t1
on t1.Loc1Time = t5.locTime
innerjoin (select distinct Loc1Time from #temp2) t2
on t2.Loc1Time = t5.locTime
innerjoin (select distinct Loc1Time from #temp3) t3
on t3.Loc1Time = t5.locTime
innerjoin (select distinct Loc1Time from #temp4) t4
on t4.Loc1Time = t5.locTime
3. Use a summing technique on a separate temp table as I described earlier:
There are other alternatives as well; you might try thinking laterally. Perhaps you can update temp table 5 (or use a separate temp table 6) where you insert 1 row from each of the other 4 temp tables. Just insert locTime, engValue. Then do the following: "SELECT locTime, Sum(engValue) From #Temp6 GROUP BY locTime HAVING Count(*) = 4". Please note that the count assumes only 1 row [datetime] is inserted from each temp table. If not, you will need to alter this approach.
The summing technique would be best if a temp table may not have a datetime that is in one of the other temp tables. That would be dependent on your process. If you only want sums where the datetime is in all 5 tables, then I would use a join. If you want all datetimes then I would look at a summing technique.
HTH. Let me know how it turns out.
Monday, March 19, 2012
How to improve performance if inner join has more than 2 or 3 tables
Hi everyone
I need a solution for this query. It is working fine for 2 tables but when there are 1000's of records in each table and query has more than 2 tables. The process never ends.
Here is the query
(select siqPid= 1007, t1.Gmt909Time as GmtTime,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as EngValue,
t1.Loc1Time as locTime,t1.msgId
into #temp5
from #temp1 as t1,#temp2 as t2,#temp3 as t3,#temp4 as t4
where t1.Loc1Time = t2.Loc1Time and t2.Loc1Time = t3.Loc1Time and t3.Loc1Time = t4.Loc1Time)
(select siqPid= 1007, t1.Gmt909Time as GmtTime,
t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
--into #temp5
from #temp1 as t1
where exists
(Select 1
from #temp2 as t2
where t1.Loc1Time = t2.Loc1Time and
exists
(Select 1
from #temp3 as t3
where t2.Loc1Time = t3.Loc1Time and
exists
(Select 1
from #temp4 as t4
where t3.Loc1Time = t4.Loc1Time))))
What do you mean by the engValues can't be added? You shouldn't have problems joining 4 tables with 1000's of records. SQL Server is designed for queries involving orders of magnitude more records. Here are some suggestions:
1. Use better join syntax. Instead of FROM A, B WHERE A.ID = B.ID use FROM A INNER JOIN B ON A.ID = B.ID.
2. Read up on Table Variables and use them instead of your temp tables. Here's a good link to read about them:http://odetocode.com/Articles/365.aspx. In particular, note that you can define primary keys for table variables which should assist in your query performance.
HTH
|||Thanks ZLA for the input
The problem is there is already alot of work done before and temp tables are crreated already. So I need to use those for this project.
Can you please help me in refining the query. I did use the syntax you asked me, but its still taking too long. Something like this
Select t1.engValue+t2.engValue+t3.engValue+t4.engValue
from #temp5 t5
inner join #temp1 t1
on t1.Loc1Time = t5.locTime
inner join #temp2 t2
on t2.Loc1Time = t5.locTime
inner join #temp3 t3
on t3.Loc1Time = t5.locTime
inner join #temp4 t4
on t4.Loc1Time = t5.locTime
but that too is taking too long... Please help me with this..
|||Have you looked at the estimated or actual execution plans for your query. In general, table scans perform slow and I suspect you have too many of them in your execution plan. It is usually better if the plan makes use of indexes.
You can create temp tables with identities, primary keys and indexes. Here is a good article that shows how:http://www.sqlteam.com/item.asp?ItemID=15442 Even though you are stuck with the temp tables, can you at least add some indexes to them to speed up the query execution?
There are other alternatives as well; you might try thinking laterally. Perhaps you can update temp table 5 (or use a separate temp table 6) where you insert 1 row from each of the other 4 temp tables. Just insert locTime, engValue. Then do the following:
SELECT locTime, Sum(engValue) From #Temp6 GROUP BY locTime HAVING Count(*) = 4
This gives the sum but only where all four tables have inserted a record (equivalent to locTime being present in all four temp tables which your inner joins select for).
HTH
|||Hi ZLA
Thats article has very good points, I can incorporate that in my process but I need to get rid of the error also in this query. Can you please help me in finding out why am I getting an error on this line and what can I do to make it work.
,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
(select siqPid= 1007, t1.Gmt909Time as GmtTime,
t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
--into #temp5
from #temp1 as t1
where exists
(Select 1
from #temp2 as t2
where t1.Loc1Time = t2.Loc1Time and
exists
(Select 1
from #temp3 as t3
where t2.Loc1Time = t3.Loc1Time and
exists
(Select 1
from #temp4 as t4
where t3.Loc1Time = t4.Loc1Time))))
You suggestions are awasome, and the last suggestion that you gave did actually worked for me, but I still want to know why the where exisit query doesnt work, if I want to add the column values from the later tables.
I would appreciate your help on this.
-Sarah
|||Is it in this last query you are still getting an error? What error are you getting? Even without that, I suspect the problem is because not all the fields exist. Using the exists clause, your query is essentially:
select siqPid= 1007, t1.Gmt909Time as GmtTime, t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
from #temp1 as t1
where exists ( ... )
This select statement only pulls selected fields from #temp1. Therefore t2, t3, t4 aren't available. They are only used in the WHERE clause. You'll need to go back to a join or use my prior idea of inserting their individual results into a new temp table (#temp6). Then sum across temp6 into temp5.
|||
How long is it taking?
How long is "too long"?
How many rows does each of the temp tables have?
|||Yes, the inner join is actually never shows up any results, it says its processing the query... I ran it with 282 records each in 4 tables, and the process never got completed, so talking about 1000 of records in each tables it far. But I will be doing that eventually, so I dont know whats wrong with the inner join statement too.
ZLA, yes, that makes sense, the exists clause probably is doing that. And the sum does work with teh group by statement. So for right now I have a solution that you suggested to sum them up. For one time its fine, but not always... as I had to put extra three query's instead of putting just one inner join query.
But to avoid future errors, is the where clause or on clause in the inner join query asking for too much, 'cause there is no clear filter like loctime = 5/6/1990...
Please let me know how else can I improve my inner join then..
Thanks
-Sarah
|||Am sorry, I was talking about this query now in my last post
Select t1.engValue+t2.engValue+t3.engValue+t4.engValue
from #temp5 t5
inner join #temp1 t1
on t1.Loc1Time = t5.locTime
inner join #temp2 t2
on t2.Loc1Time = t5.locTime
inner join #temp3 t3
on t3.Loc1Time = t5.locTime
inner join #temp4 t4
on t4.Loc1Time = t5.locTime
|||
I started to try to test this locally by creating my own test tables and realized one possible cause for your query to take so long, even with so few records. How many duplicate locTime / Loc1Time values are there across the 5 temp tables?
For example, assume the same datetime value appears 10 times in each table. Since you are only joining on the datetime field, temp5 joined to temp1 will produce 10 x 10 = 100 result rows. Since you have 5 tables, 10 duplicates could produce 100,000 rows by themselves. If your actual number of duplicates is higher than my example, you could be trying to run a query that will return a million or more records.
Try running the following to test this:
SELECT locTime, Count(*) FROM #temp5 GROUP BY locTime HAVING Count(*) > 1 ORDER BY Count(*) DESC
SELECT loc1Time, Count(*) FROM #temp1 GROUP BY loc1Time HAVING Count(*) > 1 ORDER BY Count(*) DESC
etc...
Let me know what you find out.
|||Well, I went ahead and tested this locally. For my test, I was able to join the tables, each with only 3 duplicates, to return 74,500 rows in less than a second. Below is all of the SQL I used to test this. If you are still seeing the performance problems, it makes me think either the server is way overloaded or there is some other resource constraint impeding your efforts.
Test Table Creation:
ifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T5]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T5]GOifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T1]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T1]GOifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T2]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T2]GOifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T3]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T3]GOifexists (select *from dbo.sysobjectswhere id =object_id(N'[dbo].[T4]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T4]GOCREATE TABLE [dbo].[T5] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[locTime] [datetime]NOT NULL )ON [PRIMARY]GOCREATE TABLE [dbo].[T1] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[loc1Time] [datetime]NOT NULL ,[engValue] [float]NOT NULL )ON [PRIMARY]GOCREATE TABLE [dbo].[T2] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[loc1Time] [datetime]NOT NULL ,[engValue] [float]NOT NULL )ON [PRIMARY]GOCREATE TABLE [dbo].[T3] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[loc1Time] [datetime]NOT NULL ,[engValue] [float]NOT NULL )ON [PRIMARY]GOCREATE TABLE [dbo].[T4] ([ID] [int]IDENTITY (1, 1)NOT NULL ,[loc1Time] [datetime]NOT NULL ,[engValue] [float]NOT NULL )ON [PRIMARY]GOALTER TABLE [dbo].[T5]WITH NOCHECK ADD CONSTRAINT [PK_T5]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GOALTER TABLE [dbo].[T1]WITH NOCHECK ADD CONSTRAINT [PK_T1]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GOALTER TABLE [dbo].[T2]WITH NOCHECK ADD CONSTRAINT [PK_T2]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GOALTER TABLE [dbo].[T3]WITH NOCHECK ADD CONSTRAINT [PK_T3]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GOALTER TABLE [dbo].[T4]WITH NOCHECK ADD CONSTRAINT [PK_T4]PRIMARY KEY CLUSTERED ([ID])ON [PRIMARY] GO
Test Table Population:
DECLARE @.Counterint, @.Datedatetime, @.Table intSET @.Counter = 0SET @.Date ='01/01/2001 00:00'WHILE (@.Counter < 1000)BEGIN IF @.Counter = 350or @.Counter = 700BEGIN SET @.Date ='01/01/2001 00:00'END INSERT T5 (locTime)VALUES (@.Date)SET @.Counter = @.Counter + 1SET @.Date =DateAdd(hh, 1, @.Date)ENDSET @.Table = 1WHILE (@.Table < 5)BEGIN SET @.Counter = 0SET @.Date ='01/01/2001 00:00'WHILE (@.Counter < 1000)BEGIN IF @.Counter = 350or @.Counter = 700BEGIN SET @.Date ='01/01/2001 00:00'END IF @.Table = 1INSERT T1 (loc1Time, engValue)VALUES (@.Date, rand())ELSE IF @.Table = 2INSERT T2 (loc1Time, engValue)VALUES (@.Date, rand())ELSE IF @.Table = 3INSERT T3 (loc1Time, engValue)VALUES (@.Date, rand())ELSE IF @.Table = 4INSERT T4 (loc1Time, engValue)VALUES (@.Date, rand())SET @.Counter = @.Counter + 1SET @.Date =DateAdd(hh, 1, @.Date)END SET @.Table = @.Table + 1ENDselectcount(*)from T5selectcount(*)from T1selectcount(*)from T2selectcount(*)from T3selectcount(*)from T4
Temp Table Join Test:
SELECT *INTO #temp5FROM T5SELECT *INTO #temp1FROM T1SELECT *INTO #temp2FROM T2SELECT *INTO #temp3FROM T3SELECT *INTO #temp4FROM T4Select t1.engValue+t2.engValue+t3.engValue+t4.engValuefrom #temp5 t5innerjoin #temp1 t1on t1.Loc1Time = t5.locTimeinnerjoin #temp2 t2on t2.Loc1Time = t5.locTimeinnerjoin #temp3 t3on t3.Loc1Time = t5.locTimeinnerjoin #temp4 t4on t4.Loc1Time = t5.locTimeDROP TABLE #temp5DROP TABLE #temp1DROP TABLE #temp2DROP TABLE #temp3DROP TABLE #temp4|||
Hi ZLA
Yes, that can be a possiblity because for certain locValues, I have been having probably 20-30 duplicate locId's that must be the cause of keeping the process busy.
I still need to go over the table example you showed me, I will go over it tomorrow, its been very busy at work today... I am really thankful to you for this help.
Tomorrow I will go over it and will create more duplicate records and will confirm if thats the reason of my problem.
Thanks once again
-Sarah
|||
That sounds like it's the cause, then. If you have trouble figuring out how to handle the duplicates, let me know. You'll need to decide / know what the correct answer should be. For example, if
Temp1.loc1Time = '7/1/2006 12:34', Temp1.engValue = 1.0
Temp1.loc1Time = '7/1/2006 12:34', Temp1.engValue = 2.0
Temp2.loc1Time = '7/1/2006 12:34', Temp2.engValue = 0.1
Temp2.loc1Time = '7/1/2006 12:34', Temp2.engValue = 0.2
Assume all other matching records have engValue of 0.0. Using joins, you would get 4 matches: 1.0 and 0.1, 1.0 and 0.2, 2.0 and 0.1, 2.0 and 0.2. That would give you 4 separate sums for that time: 1.1, 1.2, 2.1 and 2.2. If those four are summed together, the grand total is 6.6.
If, however, the grand total for that time should just be 3.3, then the joins are not the right method.
Let me know what you find out.
|||Hi ZLA
Yes, thats exactly whats happening, one table has 100 adn another has 98 rows with loc1time as 1/1/1900. and with teh join, when I stop in a matter of seconds its showing 14,000 rows and its still not complete.
Yes, it seems like its giving the result of 6.6 and we want 3.3. Either we need to group by the loc1time in each table adn make sure that there is one record in each of the temp tables or delete teh enteries from teh temp tables that have duplicate joining condition.
I think we are going towards your first solution, which asked for grouping in the fifth table with the expression and summing the values up.
what do you suggest?
-Sarah
|||Hurrah! I can think of several approaches:
1. Is the 1/1/1900 date valid? If not, either correct the underlying problem or exclude these records from the query. To exclude them, you could eliminate them when creating #temp1, #temp2, ... Or you could add a general where clause to the multiple inner join such as "WHERE temp1.loc1Time <> '1/1/1900' AND temp2loc1Time <> '1/1/1900' ..." Or you could modify the joins to do the filtering: "FROM #temp5 t5 INNER JOIN #temp1 t1 ON t1.Loc1Time = t5.locTime AND t1.Loc1Time <> '1/1/1900' ..." I think the first is best if possible; the last is next best.
2. If duplicate values are not correct, I would recommend building the temp tables so they only hold the distinct values you want. That should be the most efficient. If possible, you could get that by adding DISTINCT to the select statement that builds them. If you can't alter the temp table creation, you can use sub-queries in your join:
Select t1.engValue+t2.engValue+t3.engValue+t4.engValue
from (select distinct locTime from #temp5) t5
innerjoin (select distinct Loc1Time from #temp1) t1
on t1.Loc1Time = t5.locTime
innerjoin (select distinct Loc1Time from #temp2) t2
on t2.Loc1Time = t5.locTime
innerjoin (select distinct Loc1Time from #temp3) t3
on t3.Loc1Time = t5.locTime
innerjoin (select distinct Loc1Time from #temp4) t4
on t4.Loc1Time = t5.locTime
3. Use a summing technique on a separate temp table as I described earlier:
There are other alternatives as well; you might try thinking laterally. Perhaps you can update temp table 5 (or use a separate temp table 6) where you insert 1 row from each of the other 4 temp tables. Just insert locTime, engValue. Then do the following: "SELECT locTime, Sum(engValue) From #Temp6 GROUP BY locTime HAVING Count(*) = 4". Please note that the count assumes only 1 row [datetime] is inserted from each temp table. If not, you will need to alter this approach.
The summing technique would be best if a temp table may not have a datetime that is in one of the other temp tables. That would be dependent on your process. If you only want sums where the datetime is in all 5 tables, then I would use a join. If you want all datetimes then I would look at a summing technique.
HTH. Let me know how it turns out.
How to import the data from oracle to SQL Server?
Hello friends,
I am working for a project. At my college I used to work with oracle. Now as requirement changes I have to change my database to SQL Server.
Is there any simple way one can suggest me?
Wow, I believe that you may have posted in the wrong forum.
This should have been posted in the "Oracle to SQL Server in I step...Data, DDL and Procedures" forum.
|||Ryan.Kelley wrote:
Wow, I believe that you may have posted in the wrong forum.
This should have been posted in the "Oracle to SQL Server in I step...Data, DDL and Procedures" forum.
Is this meant to be helpful?
This may be of value (and other Google sites): http://www.sql-server-performance.com/np_migrating_from_oracle_to_sql_server.asp
Wednesday, March 7, 2012
How to implement remote Data Entry then upload to corp. database?
I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Greg Larsen wrote:
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want t
o
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want t
o
> upload the information to our corporate database, which is SQL Server 2000
.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
This is all supported through Merge Replication. Take a look at the
replication topics in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Greg Larsen wrote:
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want t
o
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want t
o
> upload the information to our corporate database, which is SQL Server 2000
.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
This is all supported through Merge Replication. Take a look at the
replication topics in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Subscribe to:
Posts (Atom)