|
I have to use aggregate function sum() on a table having more than 4 millions of records. It is a history table. Are there any tips to be followed. the queries are a follows
SELECT @TransferIn = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr
and trnstloc = @route and docdate between @FromDate and @ToDate
SELECT @TransferOut = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr
and trxloctn = @route and docdate between @FromDate and @ToDate
SELECT @Sales = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype=6 and itemnmbr = @itemnmbr and
trxloctn = @route
In the above query @route,@FromDate and @ToDate are user inputs
|
|
|
|
|
Dear Uma,
Please give me the table structure with the available indexes.
Senthil
|
|
|
|
|
Dear Senthil,
The table structure is as follows
[TRXSORCE] [char](13)NOT NULL,
[DOCTYPE] [smallint] NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[HSTMODUL] [char](3) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[LNSEQNBR] [numeric](19, 5) NOT NULL,
[UOFM] [char](9) NOT NULL,
[TRXQTY] [numeric](19, 5) NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[TRXLOCTN] [char](11) NOT NULL,
[TRNSTLOC] [char](11) NOT NULL,
[TRFQTYTY] [smallint] NOT NULL,
[TRTQTYTY] [smallint] NOT NULL,
[IVIVINDX] [int] NOT NULL,
[IVIVOFIX] [int] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[QTYBSUOM] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] NOT NULL
There are no indexes on this table. I cannot create any indexes as it a table used by ERP named Microsoft Dynamics GP
|
|
|
|
|
Uma Kameswari wrote: I cannot create any indexes as it a table
Then you cant really optimize the table, can you?
|
|
|
|
|
Since you don't have any indexes, you cannot create them and you don't have joins, there's not much you can do. The only thing that comes in mind is that yu copy the data to another table (perhapes in another database), index it and execute the query there. However this isn't a good solution if you need the data to be up-to-date.
|
|
|
|
|
How to transfer database from one system to other system?
|
|
|
|
|
Simply make a database backup and restore. 
|
|
|
|
|
you can do one way of these:
1-Backup from source database and Restore on destination, for this option your database server must be same
2-using Import/export tool from Integration service of Sqlserver, for this option database server doesn't matter usually.
|
|
|
|
|
Detach the Database files (.mdf and .ldf).
Move the files physically.
Attach the files to the database.
If previous log is required attach both .mdf and .ldf or else only .mdf can be attached.
|
|
|
|
|
|
Karthik,
Detaching means, removing database files from the server and files will remain in the same place without connecting to server.
Check
sp_detach_db
sp_attach_db
sp_attach_single_file_db
Senthil
|
|
|
|
|
i have 3 tables like this
t1 --->table1
t1Id t1Name
1 jj
2 qq
t2----->table2
t2Id t2Name
1 rr
2 uu
t3---table 3
t3Id tableName
1 t1
2 t2
i need the foolwing result
name
jj
uu
|
|
|
|
|
select t1.t1Name,t2.t2Name<br />
from t1,t2,t3<br />
where t1.t1id=t2.t2id <br />
and t1.t1id=t3.t3id and t2.t2id=t3.t3id
Hope it will hep you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Please give more details about the relationship between those 3 tables.
|
|
|
|
|
Good Morning Guys
I have the Following statement, that works well when i test it alone
declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] 'MasekoTS','MasterDatabase', @Results OUT
But now, i want to include this code in a Stored Procedure and i tried to do it like this
SELECT @sql1 = 'Declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] ''' + @DB + ''', ''' + @Filename + '''@Results OUT'''
execute ( @sql1 )
and i get the Following Error
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'sp_RestoreDatabase_O_Bookin'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_RestoreDatabase_O_Bookin'.
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Instead of doing execute ( @sql1 ) try Print @sql1 to see where its missing a quote - thats my usual method.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks i have Executed the statement without String Manipulation and its working
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Pleased to have been of service 
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Just a pointer to help you out in the future with debugging dynamic SQL. When you have a problem like this, rem out the EXEC(@SQL) and put a select @SQL or PRINT @SQL.
Take the returned value and attempt to exec it in a diff. query window. Compare your orig statement to the dynamic one and find the error.
I think the error in this case is the missing , after the file name.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
modified on Wednesday, January 21, 2009 8:46 AM
|
|
|
|
|
Thanks i got the Solution, i just used it without any string Manipulation
thanks for your help
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Is it sometimes better to just delete and then reinsert some records instead of updating them? I have a complex scenario which is hard to explain but I am thinking that instead of updating, deleting and inserting is a better option. Has anyone come across such a scenario where deletion and reinsertion is better than update?
|
|
|
|
|
If you are doing a delete of a set of records, as apposed to a delete of ALL records, then the table is scanned to locate the records to be deleted. The delete action also affects any indexes on the table. Insert also affects the indexes but is less costly.
Update only affects the indexes if the indexed fields are updated.
You can do both in management Studio with the execution plan and costs turned on and compare your specific process.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can do both in management Studio with the execution plan and costs turned on and compare your specific process.
Like Holmes said....if there is any doubt and you are debating the speed differences between different SQL statements, do it both ways and compare the execution path. Stats don't lie. See what the costs are and avoid scans.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Without knowing anything more on the issue, I'd say you should try to avoid delete/insert combination if the logical request is to update record(s). For example even it may be possible now, what happens if you add child tables underneath this table. Would the data in those tables be deleted/inserted also. Also I think there would be some locking related issues etc.
In most situations I've encountered this technique, it's been a modeling/architectural issue either in db or in the program or in both.
But as said, with the info you provided, it's hard to say more.
|
|
|
|
|
Consider the following:
ClothingStyle Size
============= ====
ID ID
Description Description
For example, TShirts can come in S, M, L, XL, XXL where each of these is in the Description column of Size. So the relationship here is 1:M. However, sometimes some clothes have a two dimensional size. For example, jeans can come in waist sizes of 26, 28, 30, 32, 34 and so on but can also have length. How can I create the structure for such a scenario?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
