2004-08-21 11:52:38.64 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:52:38.64 spid52 language_exec: Process 52
generated an access violation. SQL Server is terminating
this process..
2004-08-21 11:56:12.93 spid52 Using 'sqlimage.dll'
version '4.0.5'
Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQL00062.dmp
2004-08-21 11:56:12.94 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:56:12.94 spid52 SqlDumpExceptionHandler:
Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process..
************************************************** *********
********************
*
* BEGIN STACK DUMP:
* 08/21/04 11:56:12 spid 52
*
* Exception Address = 0055AB2F
(COpArg::DeriveNormalizedGroupProperties(class CTreeHandle
*) + 0000001E Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 4088 bytes -
* SELECT glmedium.module_id,
glmedium.vouchertype,
* glmedium.vouchernumber,
glmedium.voucherdate,
* glmedium.accountcode,
glmedium.amountdebit,
* glmedium.amountcredit,
glmedium.jobid, cstcon
* tract.ContractName,
glaccounts.AccountName , gljobwisep
* rofit.groupcode ,
glmedium.accountremarks,
glmedium.partycode,
* arcustomer.customername,
contractvalue,provisionamt FROM glm
* edium, cstcontract,
glaccounts, gljobwisep
* rofit, arcustomer WHERE (
glmedium.company_id = cstcontract.Co
* mpany_id ) and ( glmedium.jobid =
cstcontract.ContractNo ) a
* nd ( glmedium.company_id =
glaccounts.Company_id ) and
* ( glmedium.accountcode =
glaccounts.AccountCode ) AND VOUCHER
* TYPE NOT IN ('GI','SR','DN','DJ') and
glmedium.accountcode = gljobwi
* seprofit.accountcode and
glmedium.company_id = arcustomer.company_i
* d and partycode =
arcustomer.customercode and
glmedium.company_i
* d = '05' and
glmedium.voucherdate >= '1-JAN-2000' and
glmedium.
* voucherdate <= '31-DEC-2004' and
glmedium.jobid >= '0' and
glmed
* ium.jobid <= 'ZZZZZZZZ' UNION SELECT
glmedium.module_id,
* glmedium.vouchertype,
glmedium.vouchernumber,
* glmedium.voucherdate,
glmedium.accountcode,
* glmedium.amountdebit, 0,
glmedium.jobid,
* cstcontract.ContractName,
glaccounts.AccountName
* , 'ZI' ,
glmedium.accountremarks,
glmedium.partycode,
* arcustomer.customername,
contractvalue,provisionamt FROM glmedi
* um, cstcontract, glaccounts,
arcustomer
* WHERE ( glmedium.company_id =
cstcontract.Company_id ) and
* ( glmedium.jobid = cstcontract.ContractNo )
and ( glmed
* ium.company_id = glaccounts.Company_id )
and ( glmedium.acco
* untcode = glaccounts.Account
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF
0071a000
* ntdll 77F80000 77FFCFFF
0007d000
* KERNEL32 7C570000 7C627FFF
000b8000
* ADVAPI32 7C2D0000 7C331FFF
00062000
* RPCRT4 77D30000 77DA0FFF
00071000
* USER32 77E10000 77E74FFF
00065000
* GDI32 77F40000 77F7DFFF
0003e000
* OPENDS60 41060000 41065FFF
00006000
* MSVCRT 78000000 78044FFF
00045000
* UMS 41070000 4107CFFF
0000d000
* SQLSORT 42AE0000 42B6FFFF
00090000
* MSVCIRT 780A0000 780B1FFF
00012000
* sqlevn70 41080000 41086FFF
00007000
* NETAPI32 75170000 751BEFFF
0004f000
* Secur32 7C340000 7C34EFFF
0000f000
* NTDSAPI 77BF0000 77C00FFF
00011000
* DNSAPI 77980000 779A3FFF
00024000
* WSOCK32 75050000 75057FFF
00008000
* WS2_32 75030000 75043FFF
00014000
* WS2HELP 75020000 75027FFF
00008000
* WLDAP32 77950000 77979FFF
0002a000
* NETRAP 751C0000 751C5FFF
00006000
* SAMLIB 75150000 7515EFFF
0000f000
* wmi 76110000 76113FFF
00004000
* SSNETLIB 42CF0000 42D05FFF
00016000
* SSNMPN70 410D0000 410D5FFF
00006000
* security 75500000 75503FFF
00004000
* crypt32 7C740000 7C7C6FFF
00087000
* MSASN1 77430000 7743FFFF
00010000
* userenv 7C0F0000 7C150FFF
00061000
* rnr20 782C0000 782CBFFF
0000c000
* iphlpapi 77340000 77352FFF
00013000
* ICMP 77520000 77524FFF
00005000
* MPRAPI 77320000 77336FFF
00017000
* OLE32 77A50000 77B3EFFF
000ef000
* OLEAUT32 779B0000 77A4AFFF
0009b000
* ACTIVEDS 773B0000 773DEFFF
0002f000
* ADSLDPC 77380000 773A2FFF
00023000
* RTUTILS 77830000 7783DFFF
0000e000
* SETUPAPI 77880000 7790DFFF
0008e000
* RASAPI32 774E0000 77512FFF
00033000
* RASMAN 774C0000 774D0FFF
00011000
* TAPI32 77530000 77551FFF
00022000
* COMCTL32 71710000 71793FFF
00084000
* SHLWAPI 70A70000 70AD4FFF
00065000
* DHCPCSVC 77360000 77378FFF
00019000
* winrnr 777E0000 777E7FFF
00008000
* rasadhlp 777F0000 777F4FFF
00005000
* msafd 74FD0000 74FEDFFF
0001e000
* wshtcpip 75010000 75016FFF
00007000
* SSmsLPCn 42CD0000 42CD6FFF
00007000
* SQLFTQRY 41020000 4103CFFF
0001d000
* CLBCATQ 775A0000 7762FFFF
00090000
* SQLOLEDB 75370000 753E7FFF
00078000
* MSDART 2AD60000 2AD82FFF
00023000
* VERSION 77820000 77826FFF
00007000
* LZ32 759B0000 759B5FFF
00006000
* comdlg32 76B30000 76B6DFFF
0003e000
* SHELL32 782F0000 78534FFF
00245000
* MSDATL3 2AD90000 2ADA5FFF
00016000
* oledb32 2B0B0000 2B11EFFF
0006f000
* OLEDB32R 2B120000 2B130FFF
00011000
* rsabase 7CA00000 7CA22FFF
00023000
* xpstar 410F0000 41133FFF
00044000
* SQLUNIRL 41090000 410BCFFF
0002d000
* WINSPOOL 77800000 7781DFFF
0001e000
* MPR 76620000 7662FFFF
00010000
* SQLRESLD 42AC0000 42AC6FFF
00007000
* SQLSVC 42C40000 42C56FFF
00017000
* ODBC32 2B150000 2B184FFF
00035000
* odbcbcp 41150000 41156FFF
00007000
* W95SCM 41140000 4114BFFF
0000c000
* NDDEAPI 769A0000 769A6FFF
00007000
* odbcint 2B290000 2B2A5FFF
00016000
* clusapi 73930000 7393FFFF
00010000
* resutils 689D0000 689DCFFF
0000d000
* SQLSVC 43970000 43975FFF
00006000
* xpstar 439E0000 439EBFFF
0000c000
* msv1_0 2B600000 2B620FFF
00021000
* srchadm 60000000 60042FFF
00043000
* mssws 2B850000 2B858FFF
00009000
* athprxy 2BBB0000 2BBB7FFF
00008000
* DBGHELP 2BCF0000 2BD02FFF
00013000
* msdbi 6BE90000 6BEABFFF
0001c000
* sqlimage 4A400000 4A40CFFF
0000d000
*
* Edi: 00000000:
* Esi: 1D9F6B50: 00982848 00000002 209B8030
1DA15268 1DA12690 00000000
* Eax: 00000000:
* Ebx: 00000000:
* Ecx: 00000000:
* Edx: 2A88C228: 00000124 20940168 2090D5D0
208FE358 1D9B9838 20940168
* Eip: 0055AB2F: 50FF038B D8F74810 8940C01B
1375EC45 5FF44D8B 5B5EC38B
* Ebp: 2A88BD5C: 2A88BED0 0055A7BB 2A88BD74
00000000 1D9B9A48 00000000
* SegCs: 0000001B:
* EFlags: 00010246: 0061006A 00610076 0063005C
0061006C 00730073 00730065
* Esp: 2A88BD1C: 00000000 1D9F6B50 00000000
2A88BD38 0040204E 209B7400
* SegSs: 00000023:
************************************************** *********
********************
Short Stack Dump
0055AB2F Module(sqlservr+0015AB2F)
(COpArg::DeriveNormalizedGroupProperties(class CTreeHandle
*)+0000001E)
0055A7BB Module(sqlservr+0015A7BB)
(COptExpr::DeriveGroupProperties(unsigned long)+000000B3)
0055A765 Module(sqlservr+0015A765)
(COptExpr::DeriveGroupProperties(unsigned long)+0000005D)
0048BEA2 Module(sqlservr+0008BEA2)
(CImpRuleBaseJoinToIdxLookup::BuildSubstitutes(cla ss
COptExpr *,class CRuleContext *,class CRuleReturn *)
+00000ECC)
00589104 Module(sqlservr+00189104)
(CTask_ApplyRule::Perform(int)+00000268)
0058C939 Module(sqlservr+0018C939) (CMemo::ExecuteTasks
(class COptTask *,int,int)+0000014D)
0058CF2D Module(sqlservr+0018CF2D) (CMemo::OptimizeQuery
(class CQuery *,class COptExpr *,double *,int,int,struct
s_OptimPlans *)+0000051A)
0058CADF Module(sqlservr+0018CADF)
(COptContext::PexprSearchPlan(class COptExpr *)+00000155)
0055E100 Module(sqlservr+0015E100)
(COptContext::PcxteOptimizeQuery(class COptExpr *,class
DRgCId *)+00000B7A)
0055FFBB Module(sqlservr+0015FFBB) (CQuery::Optimize(void)
+00000416)
0055FD54 Module(sqlservr+0015FD54) (CQuery::Optimize
(unsigned long)+00000030)
005642C6 Module(sqlservr+001642C6) (CCvtTree::PqryFromTree
(class TREE *,class IMemObj *,class CRangeCollection
*,unsigned long,class CCompPlan *)+000002C4)
00564019 Module(sqlservr+00164019) (BuildQueryFromTree
(class TREE *,class IMemObj *,class IMemObj *,class
IQueryObj * *,class CRangeCollection *,unsigned long,class
CCompPlan *)+00000046)
00563F78 Module(sqlservr+00163F78) (CStmtQuery::InitQuery
(class CAlgStmt *,class CCompPlan *,unsigned long)
+0000014B)
0049DA48 Module(sqlservr+0009DA48) (CStmtSelect::Init
(class CAlgStmt *,class CCompPlan *,class IBrowseMode *)
+00000091)
00447078 Module(sqlservr+00047078) (CCompPlan::FCompileStep
(class CAlgStmt *,class CStatement * *)+00000AE7)
004510FE Module(sqlservr+000510FE) (CProchdr::FCompile
(class CCompPlan *,class CParamExchange *)+00000D15)
00415080 Module(sqlservr+00015080) (CSQLSource::FTransform
(class CParamExchange *)+0000037C)
004592CE Module(sqlservr+000592CE) (CSQLStrings::FTransform
(class CParamExchange *)+000001A8)
0041534F Module(sqlservr+0001534F) (CSQLSource::Execute
(class CParamExchange *)+00000176)
00459A54 Module(sqlservr+00059A54) (language_exec(struct
srv_proc *)+000003C8)
004175D8 Module(sqlservr+000175D8) (process_commands
(struct srv_proc *)+000000E0)
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class
UmsWorkQueue *)+00000264)
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)
+000000BC)
78008454 Module(MSVCRT+00008454) (_endthread+000000C1)
7C57438B Module(KERNEL32+0000438B) (TlsSetValue+000000F0)
2004-08-21 11:56:16.43 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:56:16.43 spid52 language_exec: Process 52
generated an access violation. SQL Server is terminating
this process..
You may want to contact PSS with details on how this occured:
http://support.microsoft.com/default...s;Prodoffer41a
( If this is due to a bug, you may get your money refunded )
Anith
2012年3月22日星期四
2012年3月20日星期二
Any way to programmatically deploy an SSAS solution?
Hi all,
I'm trying to add a nightly build/deploy process for my SSAS solution. Basically - to take my AS solution from source control, deploy it to our dev server, and process just a few partitions.
What's the best way to do this? Looking at the solution folder, the .dsv, .dim, .cube, .ds files are all xml-based, which is good, but they don't seem like XML/A. (Seem very similar to a serialized version of AMO objects, but not quite exactly)
The only way I'd guess to do this is to manually load the individual xml files into an object model, and then map them to the AMO objects (since they're pretty similar..), and deploy those to the server. Is there a more efficient way to directly de-serialize the .dim,.cube, etc files into the AMO structures and deploy?
Take a look at the Deployment Wizard which you can run from Start... Programs... Microsoft SQL Server 2005... Analysis Services... Deployment Wizard.
Also see more info including command line switches here:
http://msdn2.microsoft.com/en-us/library/ms174817.aspx
|||Furmangg, perfect tip on using the deployment wizard. I think this will suit our needs very well.
I was going to follow up w/ the correct method of compiling - I tried msbuild unsuccessfully, but I found this great blog post by Thomas Kejser -
http://schastar.spaces.live.com/blog/cns!12BCB785A5D8B3D4!148.entry?beid=cns!12BCB785A5D8B3D4!148&d=1&wa=wsignin1.0
Sweet!
Any way to make a stored procedure process asynchronously?
I'd like to know if there is any way to get a stored procedure to process
asynchronously. Ideally, I would kick off the procedure from within a
trigger. I would not need any return values or need to worry about
transactions.We do it by creating one-time jobs in our system. a bit cumbersome but
works.
Peter
"Random" <cipherlad@.hotmail.com> wrote in message
news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
> I'd like to know if there is any way to get a stored procedure to process
> asynchronously. Ideally, I would kick off the procedure from within a
> trigger. I would not need any return values or need to worry about
> transactions.
>|||Consider using Service Broker for this (assuming you are on 2005, no version
mentioned in the
OP...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Random" <cipherlad@.hotmail.com> wrote in message news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl
..
> I'd like to know if there is any way to get a stored procedure to process
asynchronously.
> Ideally, I would kick off the procedure from within a trigger. I would no
t need any return values
> or need to worry about transactions.
>|||Service Broker would be IDEAL if we were on version 2005. Unfortunately, we
cannot mandate at this time that all our clients move to 2005, so we are on
2000 for this.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23DlND0gQGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Consider using Service Broker for this (assuming you are on 2005, no
> version mentioned in the OP...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
>|||SQL Server is a database management system, it will typically process things
in sequential order and not asynchronously.
ADO supports the execution stored procedures, SQL, etc. asynchronously, so
perhaps the sequence of events would best be called from the application
side.
http://support.microsoft.com/defaul...kb;en-us;194960
http://msdn.microsoft.com/library/d...nc
2.asp
http://msdn2.microsoft.com/en-us/library/zw97wx20.aspx
Here is an article describing various methods of calling a DTS package from
T-SQL, including the option of calling (starting) a job asynchronously from
a trigger.
http://www.sqldts.com/default.aspx?219
I don't know what the cirsumstances or exact requirements are, but if it is
not important that the stored procedure complete within a specific time
window or within a transaction, then I have in the past implemented a table
that schedules tasks through the insertion of rows. A job can then be
scheduled to poll the table at intervals and execute the procedure calls as
needed. An added benefit is that the table itself is sort of a meta data
history of when the task has been performed.
"Random" <cipherlad@.hotmail.com> wrote in message
news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
> I'd like to know if there is any way to get a stored procedure to process
> asynchronously. Ideally, I would kick off the procedure from within a
> trigger. I would not need any return values or need to worry about
> transactions.
>|||I haven't ever tried this myself but I've heard of people using jobs to do
this and have the first stored proc manually start a job which kicks off the
second SP. This is obviously a lot less efficient than Service Broker but
it should work.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Random" <cipherlad@.hotmail.com> wrote in message
news:eR33oUhQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Service Broker would be IDEAL if we were on version 2005. Unfortunately,
> we cannot mandate at this time that all our clients move to 2005, so we
> are on 2000 for this.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23DlND0gQGHA.5808@.TK2MSFTNGP12.phx.gbl...
>
asynchronously. Ideally, I would kick off the procedure from within a
trigger. I would not need any return values or need to worry about
transactions.We do it by creating one-time jobs in our system. a bit cumbersome but
works.
Peter
"Random" <cipherlad@.hotmail.com> wrote in message
news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
> I'd like to know if there is any way to get a stored procedure to process
> asynchronously. Ideally, I would kick off the procedure from within a
> trigger. I would not need any return values or need to worry about
> transactions.
>|||Consider using Service Broker for this (assuming you are on 2005, no version
mentioned in the
OP...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Random" <cipherlad@.hotmail.com> wrote in message news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl
..
> I'd like to know if there is any way to get a stored procedure to process
asynchronously.
> Ideally, I would kick off the procedure from within a trigger. I would no
t need any return values
> or need to worry about transactions.
>|||Service Broker would be IDEAL if we were on version 2005. Unfortunately, we
cannot mandate at this time that all our clients move to 2005, so we are on
2000 for this.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23DlND0gQGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Consider using Service Broker for this (assuming you are on 2005, no
> version mentioned in the OP...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
>|||SQL Server is a database management system, it will typically process things
in sequential order and not asynchronously.
ADO supports the execution stored procedures, SQL, etc. asynchronously, so
perhaps the sequence of events would best be called from the application
side.
http://support.microsoft.com/defaul...kb;en-us;194960
http://msdn.microsoft.com/library/d...nc
2.asp
http://msdn2.microsoft.com/en-us/library/zw97wx20.aspx
Here is an article describing various methods of calling a DTS package from
T-SQL, including the option of calling (starting) a job asynchronously from
a trigger.
http://www.sqldts.com/default.aspx?219
I don't know what the cirsumstances or exact requirements are, but if it is
not important that the stored procedure complete within a specific time
window or within a transaction, then I have in the past implemented a table
that schedules tasks through the insertion of rows. A job can then be
scheduled to poll the table at intervals and execute the procedure calls as
needed. An added benefit is that the table itself is sort of a meta data
history of when the task has been performed.
"Random" <cipherlad@.hotmail.com> wrote in message
news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
> I'd like to know if there is any way to get a stored procedure to process
> asynchronously. Ideally, I would kick off the procedure from within a
> trigger. I would not need any return values or need to worry about
> transactions.
>|||I haven't ever tried this myself but I've heard of people using jobs to do
this and have the first stored proc manually start a job which kicks off the
second SP. This is obviously a lot less efficient than Service Broker but
it should work.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Random" <cipherlad@.hotmail.com> wrote in message
news:eR33oUhQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Service Broker would be IDEAL if we were on version 2005. Unfortunately,
> we cannot mandate at this time that all our clients move to 2005, so we
> are on 2000 for this.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23DlND0gQGHA.5808@.TK2MSFTNGP12.phx.gbl...
>
Any way to get processing status when executing a large batch process job via AMO?
When making an ExecuteCaptureLog() AMO call, is there any way the client can poll for processing status from the SSAS insance?
I think the only way you could do this is to capture the Trace events
This thread has some samples showing you how to create and use Trace events from code.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=482459&SiteID=1
2012年3月11日星期日
Any Timeout setting in ConnectionString of web.config
Hi:
I have some query that takes quite a long time to process in the sql server and every time the page seems to time out.
I wondor is there any Timeout setting that I can defined in the database ConnectionString in web.config file so that I can extend the "wait" time?
Many thanks!
I believe there is:
"integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Connect Timeout=45;";
|||Jason,
I recently had a time out issue with an app after some servers where moved to a centralized location. In my case, the use of the sql server name caused my app to time out as apparently the newly located web server wasn't able to resolve the sql server name in the web.config file fast enough against the WINS server. The solution was to use our sql server's IP address in web.congif file instead of it's name. Then the app ran just fine.
Time outs can definitely be network related. Need to look at that as well as any app configuring.
|||Unfortunately not Jason. The timeout in the connection string only controls the timeout of the connection (How long the connection will wait before giving up), normally it's irrelevant as you'll get a (fairly) quick negative response.
That said, in your pages where the default 30 second command timeout is too short, in your SqlDataSource_Selecting/Inserting events, the e parameter will have a reference to the actualy SqlCommand object that is about to be used. Set it's timeout. Off the top of my head, I believe it is like:
e.SelectCommand.Timeout=90
That is assuming that you are having the issue with a SqlDataSource. SqlCommand objects have their own timeout parameter that you can set if that is what you are using and having an issue with.
|||Actually, e.Command.CommandTimeout=90
Any suggestions on how to process very large files?
I am receiving large XML files from an external source. I need to inseert
all the <DATA> elements into a table on SQL Server. These files can be as
small as a couple k or as large as 3 megs. I would like to input all the data
using sprocs on the database server. Can I really really do that or should I
just write a client side app to read the data and insert into the table?
(An example is attached with just 3 data points. Most have tens of thousands
of data points.)
What is the most effiecent approach to handling large XML files? What
technology works the best? If there is information I should be reading on
this I do not seem to be able to locate it in MSDN.
Any help will be appreciated.
AlanS
<?xml version="1.0" encoding="utf-8" ?>
<ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
CAISO
<REPORT_ITEM>
<HEADER>
<REPORT>AS_FINAL_MCP</REPORT>
<SYSTEM>OASIS</SYSTEM>
<TZ>PPT</TZ>
<MKT_TYPE>A</MKT_TYPE>
<UOM>US$/MW</UOM>
<INTERVAL>ENDING</INTERVAL>
<SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
</HEADER>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>1</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>2</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>3</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
</REPORT_ITEM>
<DISCLAIMER_ITEM>
<DISCLAIMER>The contents of these pages are subject to change without
notice. Decisions based on information contained within the web site are the
visitor's sole responsibility.
</DISCLAIMER>
</DISCLAIMER_ITEM>
</ORGANIZATION>
If you have a dedicated machine with otherwise no load, you should be able
to use OpenXML.
If you have other loads on your machine or the data is often larger than a
couple of 100kB, you may want to consider the XML Bulkload object.
Best regards
Michael
"AlanS" <AlanS@.discussions.microsoft.com> wrote in message
news:BB511473-A60B-4E4F-B492-F050D8024B65@.microsoft.com...
>I am receiving large XML files from an external source. I need to inseert
> all the <DATA> elements into a table on SQL Server. These files can be as
> small as a couple k or as large as 3 megs. I would like to input all the
> data
> using sprocs on the database server. Can I really really do that or should
> I
> just write a client side app to read the data and insert into the table?
> (An example is attached with just 3 data points. Most have tens of
> thousands
> of data points.)
> What is the most effiecent approach to handling large XML files? What
> technology works the best? If there is information I should be reading on
> this I do not seem to be able to locate it in MSDN.
> Any help will be appreciated.
> AlanS
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
> xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
> CAISO
> <REPORT_ITEM>
> <HEADER>
> <REPORT>AS_FINAL_MCP</REPORT>
> <SYSTEM>OASIS</SYSTEM>
> <TZ>PPT</TZ>
> <MKT_TYPE>A</MKT_TYPE>
> <UOM>US$/MW</UOM>
> <INTERVAL>ENDING</INTERVAL>
> <SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
> </HEADER>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>1</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>2</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>3</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> </REPORT_ITEM>
> <DISCLAIMER_ITEM>
> <DISCLAIMER>The contents of these pages are subject to change without
> notice. Decisions based on information contained within the web site are
> the
> visitor's sole responsibility.
> </DISCLAIMER>
> </DISCLAIMER_ITEM>
> </ORGANIZATION>
>
|||Hello, Michael!
You wrote on Fri, 29 Oct 2004 18:30:10 -0700:
MRM> If you have other loads on your machine or the data is often larger
MRM> than a couple of 100kB, you may want to consider the XML Bulkload
MRM> object.
Since the original authers xml format is very simple I would suggest to use
SqlXmlBulkLoad object and nothing else.
With best regards, Alex Shirshov.
|||Ok. I have tried to use SQLXmlBulkLoad. But, I am having a problem. First
point though. I am told that this is an invalid connection string.
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=DEVELOPER;packet
size=4096;integrated security=SSPI;data source=ALANS;persist security
info=False;initial catalog=Llama";
The connection string works. I use it earlier to connect to populate a grid.
Here is my using statement;
using SQLXMLBULKLOADLib;
Here is my code
(1) SQLXMLBulkLoad objBL = new SQLXMLBulkLoad();
(2) objBL.ConnectionString = this.sqlConnection1.ConnectionString;
(3) objBL.ErrorLogFile = @."C:\APSES\error.log";
(4)objBL.Execute(@."C:\APSES\XML TestsSampleSchema.xml", @."C:\APSES\XML
TestsSampleXMLData.xml");
(5)objBL = null;
Exception occurs at line (4). Invalid Connect String.
Here is the error log
<?xml version="1.0"?>
<Result State="FAILED">
<Error><HResult>0x80040E21I32</HResult>
<Description><![CDATA[Invalid connection string.]]></Description>
<Source>XML BulkLoad for SQL Server</Source>
<Type>FATAL</Type>
</Error>
</Result State>
What is incorrect and how do I correct it?
all the <DATA> elements into a table on SQL Server. These files can be as
small as a couple k or as large as 3 megs. I would like to input all the data
using sprocs on the database server. Can I really really do that or should I
just write a client side app to read the data and insert into the table?
(An example is attached with just 3 data points. Most have tens of thousands
of data points.)
What is the most effiecent approach to handling large XML files? What
technology works the best? If there is information I should be reading on
this I do not seem to be able to locate it in MSDN.
Any help will be appreciated.
AlanS
<?xml version="1.0" encoding="utf-8" ?>
<ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
CAISO
<REPORT_ITEM>
<HEADER>
<REPORT>AS_FINAL_MCP</REPORT>
<SYSTEM>OASIS</SYSTEM>
<TZ>PPT</TZ>
<MKT_TYPE>A</MKT_TYPE>
<UOM>US$/MW</UOM>
<INTERVAL>ENDING</INTERVAL>
<SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
</HEADER>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>1</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>2</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>3</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
</REPORT_ITEM>
<DISCLAIMER_ITEM>
<DISCLAIMER>The contents of these pages are subject to change without
notice. Decisions based on information contained within the web site are the
visitor's sole responsibility.
</DISCLAIMER>
</DISCLAIMER_ITEM>
</ORGANIZATION>
If you have a dedicated machine with otherwise no load, you should be able
to use OpenXML.
If you have other loads on your machine or the data is often larger than a
couple of 100kB, you may want to consider the XML Bulkload object.
Best regards
Michael
"AlanS" <AlanS@.discussions.microsoft.com> wrote in message
news:BB511473-A60B-4E4F-B492-F050D8024B65@.microsoft.com...
>I am receiving large XML files from an external source. I need to inseert
> all the <DATA> elements into a table on SQL Server. These files can be as
> small as a couple k or as large as 3 megs. I would like to input all the
> data
> using sprocs on the database server. Can I really really do that or should
> I
> just write a client side app to read the data and insert into the table?
> (An example is attached with just 3 data points. Most have tens of
> thousands
> of data points.)
> What is the most effiecent approach to handling large XML files? What
> technology works the best? If there is information I should be reading on
> this I do not seem to be able to locate it in MSDN.
> Any help will be appreciated.
> AlanS
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
> xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
> CAISO
> <REPORT_ITEM>
> <HEADER>
> <REPORT>AS_FINAL_MCP</REPORT>
> <SYSTEM>OASIS</SYSTEM>
> <TZ>PPT</TZ>
> <MKT_TYPE>A</MKT_TYPE>
> <UOM>US$/MW</UOM>
> <INTERVAL>ENDING</INTERVAL>
> <SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
> </HEADER>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>1</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>2</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>3</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> </REPORT_ITEM>
> <DISCLAIMER_ITEM>
> <DISCLAIMER>The contents of these pages are subject to change without
> notice. Decisions based on information contained within the web site are
> the
> visitor's sole responsibility.
> </DISCLAIMER>
> </DISCLAIMER_ITEM>
> </ORGANIZATION>
>
|||Hello, Michael!
You wrote on Fri, 29 Oct 2004 18:30:10 -0700:
MRM> If you have other loads on your machine or the data is often larger
MRM> than a couple of 100kB, you may want to consider the XML Bulkload
MRM> object.
Since the original authers xml format is very simple I would suggest to use
SqlXmlBulkLoad object and nothing else.
With best regards, Alex Shirshov.
|||Ok. I have tried to use SQLXmlBulkLoad. But, I am having a problem. First
point though. I am told that this is an invalid connection string.
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=DEVELOPER;packet
size=4096;integrated security=SSPI;data source=ALANS;persist security
info=False;initial catalog=Llama";
The connection string works. I use it earlier to connect to populate a grid.
Here is my using statement;
using SQLXMLBULKLOADLib;
Here is my code
(1) SQLXMLBulkLoad objBL = new SQLXMLBulkLoad();
(2) objBL.ConnectionString = this.sqlConnection1.ConnectionString;
(3) objBL.ErrorLogFile = @."C:\APSES\error.log";
(4)objBL.Execute(@."C:\APSES\XML TestsSampleSchema.xml", @."C:\APSES\XML
TestsSampleXMLData.xml");
(5)objBL = null;
Exception occurs at line (4). Invalid Connect String.
Here is the error log
<?xml version="1.0"?>
<Result State="FAILED">
<Error><HResult>0x80040E21I32</HResult>
<Description><![CDATA[Invalid connection string.]]></Description>
<Source>XML BulkLoad for SQL Server</Source>
<Type>FATAL</Type>
</Error>
</Result State>
What is incorrect and how do I correct it?
2012年3月8日星期四
Any SQL experts?
Hi, I have an SP that was working fine (took about 10 mins to process).
I've added a section of the SP below for reference. Here is an overview of
my table structure (simplified).
EMPLOYEE TABLE
employeeid
name
IsActive
EMPLOYEE HISTORY TABLE
employeeid
name
IsActive
FromDate -- this signifies the start date the current employee record info
was valid.
ToDate -- this signifies the end date the current employee record info was
valid.
PROCESS TABLE
processid
name
startdate
enddate
Basically, this is what the SP is meant to do...... when the sp runs, it
will only carry out processes where the current date is between startdate
and enddate of the process record. And it should only process employees who
were active between these dates. I can find out backdated info about an
employee in the history table. However, if there have been no changes to an
employee (ie: new joiner), then there will be no records in the history
table so I have to read the current status from the master employee table.
Hope all this makes sense.
OLD CODE:
-- this works fairly quickly, but is not correct as it's
not looking at historic employee data
(employees.IsActive = 1)
NEW CODE:
(
--if a history record exists for current
employee, the read the info
(
(select top
1 IsActive
from
employeehistory eh
where
eh.todate > process.enddate
and
employeeid = employee.employeeid
order by
eh.enddate) = 1
)
OR
--if no history record exists
for current employee, then read current employee
--data in master employee table
(
not exists
(select *
from
employeehistory
where
employeeid = employee.employeeid
)
AND
(employees.IsActive
= 1)
)
)
The changed section has increased the process time massively. Furthermore,
it is doing a lot of work on tempdb and hence using up all the harddisk
space (and eventually fails). The SP is processing approx 500,000 records.
Does anyone know why this would happen, and what I can do to improve the
performace. What is it writing to tempdb? I've tried adding indexes to the
date/employee columns in the history table, but it doesn't help.Well, iterating over 500000 records will take a long time. You don't
provide the code of your SP; so we don't know how the iteration is taking
place and make it hard to give you any relevant help; however, using an
UNION instead of a Cursor for example might be a solution in your case.
Finally, I really don't understand why you have put an Order By in a
subquery.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"amy" <amy@.nospam.com> wrote in message
news:uyBhOeCUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hi, I have an SP that was working fine (took about 10 mins to process).
> I've added a section of the SP below for reference. Here is an overview
> of my table structure (simplified).
> EMPLOYEE TABLE
> employeeid
> name
> IsActive
> EMPLOYEE HISTORY TABLE
> employeeid
> name
> IsActive
> FromDate -- this signifies the start date the current employee record info
> was valid.
> ToDate -- this signifies the end date the current employee record info was
> valid.
> PROCESS TABLE
> processid
> name
> startdate
> enddate
> Basically, this is what the SP is meant to do...... when the sp runs, it
> will only carry out processes where the current date is between startdate
> and enddate of the process record. And it should only process employees
> who were active between these dates. I can find out backdated info about
> an employee in the history table. However, if there have been no changes
> to an employee (ie: new joiner), then there will be no records in the
> history table so I have to read the current status from the master
> employee table.
> Hope all this makes sense.
>
> OLD CODE:
> -- this works fairly quickly, but is not correct as
> it's not looking at historic employee data
> (employees.IsActive = 1)
>
> NEW CODE:
> (
> --if a history record exists for
> current employee, the read the info
> (
> (select top
> 1 IsActive
> from
> employeehistory eh
> where
> eh.todate > process.enddate
> and
> employeeid = employee.employeeid
> order by
> eh.enddate) = 1
> )
> OR
>
> --if no history record exists
> for current employee, then read current employee
> --data in master employee table
> (
> not exists
> (select *
> from
> employeehistory
> where
> employeeid = employee.employeeid
> )
> AND
> (employees.IsActive = 1)
> )
> )
>
> The changed section has increased the process time massively.
> Furthermore, it is doing a lot of work on tempdb and hence using up all
> the harddisk space (and eventually fails). The SP is processing approx
> 500,000 records. Does anyone know why this would happen, and what I can do
> to improve the performace. What is it writing to tempdb? I've tried
> adding indexes to the date/employee columns in the history table, but it
> doesn't help.
>
>|||Thanks for your response. There are no cursors being used, just straight
forward selects/joins. The full SP is very long, I have only included the
part that has changed and is making the tempdb grow massively.
The order by is required because I need to find the 1st instance of the
employee history record after the process date.
Eg:
If the process date is 3rd feb and the employee history is:
empid name IsActive fromdate todate
2 tom 0 20th feb 20th march
2 tom 1 1st feb 20th feb
2 tom 0 29th jan 1st feb
2 tom 1 1st jan 29th jan
then i need to find the state of the employee as at 3rd feb. i do this by
finding the first record where the enddate > 3rd feb.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OIqBtGEUGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Well, iterating over 500000 records will take a long time. You don't
> provide the code of your SP; so we don't know how the iteration is taking
> place and make it hard to give you any relevant help; however, using an
> UNION instead of a Cursor for example might be a solution in your case.
> Finally, I really don't understand why you have put an Order By in a
> subquery.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "amy" <amy@.nospam.com> wrote in message
> news:uyBhOeCUGHA.4452@.TK2MSFTNGP12.phx.gbl...
>|||amy
It's hard to suggest without seeing the whole code and understand all
business requirements
I'd start looking at an execution plan , whether or not the optimizer ia
available to use indexes
Aaron has a great article at his web site
http://www.aspfaq.com/show.asp?id=2446
"amy" <amy@.nospam.com> wrote in message
news:O46x64GUGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Thanks for your response. There are no cursors being used, just straight
> forward selects/joins. The full SP is very long, I have only included the
> part that has changed and is making the tempdb grow massively.
> The order by is required because I need to find the 1st instance of the
> employee history record after the process date.
> Eg:
> If the process date is 3rd feb and the employee history is:
> empid name IsActive fromdate todate
> 2 tom 0 20th feb 20th march
> 2 tom 1 1st feb 20th feb
> 2 tom 0 29th jan 1st feb
> 2 tom 1 1st jan 29th jan
> then i need to find the state of the employee as at 3rd feb. i do this by
> finding the first record where the enddate > 3rd feb.
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:OIqBtGEUGHA.5468@.TK2MSFTNGP14.phx.gbl...
>|||[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
amy (amy@.nospam.com) writes:
> The order by is required because I need to find the 1st instance of the
> employee history record after the process date.
> Eg:
> If the process date is 3rd feb and the employee history is:
> empid name IsActive fromdate todate
> 2 tom 0 20th feb 20th march
> 2 tom 1 1st feb 20th feb
> 2 tom 0 29th jan 1st feb
> 2 tom 1 1st jan 29th jan
> then i need to find the state of the employee as at 3rd feb. i do this by
> finding the first record where the enddate > 3rd feb.
The standard idiom is something like:
SELECT eh.issactive
FROM (SELECT * FROM process WHERE processid = @.processid)
CROSS JOIN (employees e
JOIN employeehistory eh
ON e.employessid = eh.empolyeeid
AND e.startdate = (SELECT MAX(eh2.employeedate)
FROM employeehistory eh2
WHERE eh2.empoloyeeid = eh.employessid
AND e.employeedate <= p.processdate)
Unforteunately, this is not going to perform very well. I guess it is
not possible for you change the tables, but for this sort of operation,
it can be far more effecient to have one row per employee and day, even
if it takes up a lot more disk space.
It helps if you include CREATE TABLE and CREATE INDEX statements for your
tables. Also sample data as INSERT statements with sample data is good,
as that helps to test the logic of a query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Amy
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful
DDL and example data. You don't say what should happen if the employee was
only active for part of the period. This will say if the employee was active
at the start of the period or currently active (but not necessarily an
employee at the time of the period) which may be a starting place.
SELECT
e.employeeid
e.name
[EMPLOYEE TABLE] e
JOIN [EMPLOYEE HISTORY TABLE] h ON h.employeeid = e.employeeid AND
h.IsActive = 1 AND ((h.FromDate <= @.FromDate AND h.ToDate >= @.FromDate)
UNION ALL
SELECT
e.employeeid
e.name
[EMPLOYEE TABLE] e
WHERE NOT EXISTS ( SELECT * FROM [EMPLOYEE HISTORY TABLE] h WHERE
h.employeeid = e.employeeid )
AND e.IsActive = 1
John
"amy" wrote:
> Hi, I have an SP that was working fine (took about 10 mins to process).
> I've added a section of the SP below for reference. Here is an overview o
f
> my table structure (simplified).
> EMPLOYEE TABLE
> employeeid
> name
> IsActive
> EMPLOYEE HISTORY TABLE
> employeeid
> name
> IsActive
> FromDate -- this signifies the start date the current employee record info
> was valid.
> ToDate -- this signifies the end date the current employee record info was
> valid.
> PROCESS TABLE
> processid
> name
> startdate
> enddate
> Basically, this is what the SP is meant to do...... when the sp runs, it
> will only carry out processes where the current date is between startdate
> and enddate of the process record. And it should only process employees w
ho
> were active between these dates. I can find out backdated info about an
> employee in the history table. However, if there have been no changes to a
n
> employee (ie: new joiner), then there will be no records in the history
> table so I have to read the current status from the master employee table.
> Hope all this makes sense.
>
> OLD CODE:
> -- this works fairly quickly, but is not correct as it
's
> not looking at historic employee data
> (employees.IsActive = 1)
>
> NEW CODE:
> (
> --if a history record exists for curre
nt
> employee, the read the info
> (
> (select to
p
> 1 IsActive
> from
> employeehistory eh
> where
> eh.todate > process.enddate
> and
> employeeid = employee.employeeid
> order by
> eh.enddate) = 1
> )
> OR
>
> --if no history record exists
> for current employee, then read current employee
> --data in master employee tabl
e
> (
> not exists
> (select *
> from
> employeehistory
> where
> employeeid = employee.employeeid
> )
> AND
> (employees
.IsActive
> = 1)
> )
> )
>
> The changed section has increased the process time massively. Furthermore
,
> it is doing a lot of work on tempdb and hence using up all the harddisk
> space (and eventually fails). The SP is processing approx 500,000 records
.
> Does anyone know why this would happen, and what I can do to improve the
> performace. What is it writing to tempdb? I've tried adding indexes to t
he
> date/employee columns in the history table, but it doesn't help.
>
>
>|||Depending on the circumstances, joining on a sub-query can result in
performance issues.
Instead of doing this:
not exists (select * from employeehistory where employeeid =
employee.employeeid)
Consider doing this:
left join employeehistory on employeehistory.employeeid =
employee.employeeid
Also consider inserting relevent transactions from employeehistory into a
temporary tables and joining with that.
You can use the Display Estimated Execution Plan feature of Query Analyzer
to determine what lookups and indexes are used and compare different
versions of a SQL statement:
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"amy" <amy@.nospam.com> wrote in message
news:uyBhOeCUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hi, I have an SP that was working fine (took about 10 mins to process).
> I've added a section of the SP below for reference. Here is an overview
> of my table structure (simplified).
> EMPLOYEE TABLE
> employeeid
> name
> IsActive
> EMPLOYEE HISTORY TABLE
> employeeid
> name
> IsActive
> FromDate -- this signifies the start date the current employee record info
> was valid.
> ToDate -- this signifies the end date the current employee record info was
> valid.
> PROCESS TABLE
> processid
> name
> startdate
> enddate
> Basically, this is what the SP is meant to do...... when the sp runs, it
> will only carry out processes where the current date is between startdate
> and enddate of the process record. And it should only process employees
> who were active between these dates. I can find out backdated info about
> an employee in the history table. However, if there have been no changes
> to an employee (ie: new joiner), then there will be no records in the
> history table so I have to read the current status from the master
> employee table.
> Hope all this makes sense.
>
> OLD CODE:
> -- this works fairly quickly, but is not correct as
> it's not looking at historic employee data
> (employees.IsActive = 1)
>
> NEW CODE:
> (
> --if a history record exists for
> current employee, the read the info
> (
> (select top
> 1 IsActive
> from
> employeehistory eh
> where
> eh.todate > process.enddate
> and
> employeeid = employee.employeeid
> order by
> eh.enddate) = 1
> )
> OR
>
> --if no history record exists
> for current employee, then read current employee
> --data in master employee table
> (
> not exists
> (select *
> from
> employeehistory
> where
> employeeid = employee.employeeid
> )
> AND
> (employees.IsActive = 1)
> )
> )
>
> The changed section has increased the process time massively.
> Furthermore, it is doing a lot of work on tempdb and hence using up all
> the harddisk space (and eventually fails). The SP is processing approx
> 500,000 records. Does anyone know why this would happen, and what I can do
> to improve the performace. What is it writing to tempdb? I've tried
> adding indexes to the date/employee columns in the history table, but it
> doesn't help.
>
>
I've added a section of the SP below for reference. Here is an overview of
my table structure (simplified).
EMPLOYEE TABLE
employeeid
name
IsActive
EMPLOYEE HISTORY TABLE
employeeid
name
IsActive
FromDate -- this signifies the start date the current employee record info
was valid.
ToDate -- this signifies the end date the current employee record info was
valid.
PROCESS TABLE
processid
name
startdate
enddate
Basically, this is what the SP is meant to do...... when the sp runs, it
will only carry out processes where the current date is between startdate
and enddate of the process record. And it should only process employees who
were active between these dates. I can find out backdated info about an
employee in the history table. However, if there have been no changes to an
employee (ie: new joiner), then there will be no records in the history
table so I have to read the current status from the master employee table.
Hope all this makes sense.
OLD CODE:
-- this works fairly quickly, but is not correct as it's
not looking at historic employee data
(employees.IsActive = 1)
NEW CODE:
(
--if a history record exists for current
employee, the read the info
(
(select top
1 IsActive
from
employeehistory eh
where
eh.todate > process.enddate
and
employeeid = employee.employeeid
order by
eh.enddate) = 1
)
OR
--if no history record exists
for current employee, then read current employee
--data in master employee table
(
not exists
(select *
from
employeehistory
where
employeeid = employee.employeeid
)
AND
(employees.IsActive
= 1)
)
)
The changed section has increased the process time massively. Furthermore,
it is doing a lot of work on tempdb and hence using up all the harddisk
space (and eventually fails). The SP is processing approx 500,000 records.
Does anyone know why this would happen, and what I can do to improve the
performace. What is it writing to tempdb? I've tried adding indexes to the
date/employee columns in the history table, but it doesn't help.Well, iterating over 500000 records will take a long time. You don't
provide the code of your SP; so we don't know how the iteration is taking
place and make it hard to give you any relevant help; however, using an
UNION instead of a Cursor for example might be a solution in your case.
Finally, I really don't understand why you have put an Order By in a
subquery.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"amy" <amy@.nospam.com> wrote in message
news:uyBhOeCUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hi, I have an SP that was working fine (took about 10 mins to process).
> I've added a section of the SP below for reference. Here is an overview
> of my table structure (simplified).
> EMPLOYEE TABLE
> employeeid
> name
> IsActive
> EMPLOYEE HISTORY TABLE
> employeeid
> name
> IsActive
> FromDate -- this signifies the start date the current employee record info
> was valid.
> ToDate -- this signifies the end date the current employee record info was
> valid.
> PROCESS TABLE
> processid
> name
> startdate
> enddate
> Basically, this is what the SP is meant to do...... when the sp runs, it
> will only carry out processes where the current date is between startdate
> and enddate of the process record. And it should only process employees
> who were active between these dates. I can find out backdated info about
> an employee in the history table. However, if there have been no changes
> to an employee (ie: new joiner), then there will be no records in the
> history table so I have to read the current status from the master
> employee table.
> Hope all this makes sense.
>
> OLD CODE:
> -- this works fairly quickly, but is not correct as
> it's not looking at historic employee data
> (employees.IsActive = 1)
>
> NEW CODE:
> (
> --if a history record exists for
> current employee, the read the info
> (
> (select top
> 1 IsActive
> from
> employeehistory eh
> where
> eh.todate > process.enddate
> and
> employeeid = employee.employeeid
> order by
> eh.enddate) = 1
> )
> OR
>
> --if no history record exists
> for current employee, then read current employee
> --data in master employee table
> (
> not exists
> (select *
> from
> employeehistory
> where
> employeeid = employee.employeeid
> )
> AND
> (employees.IsActive = 1)
> )
> )
>
> The changed section has increased the process time massively.
> Furthermore, it is doing a lot of work on tempdb and hence using up all
> the harddisk space (and eventually fails). The SP is processing approx
> 500,000 records. Does anyone know why this would happen, and what I can do
> to improve the performace. What is it writing to tempdb? I've tried
> adding indexes to the date/employee columns in the history table, but it
> doesn't help.
>
>|||Thanks for your response. There are no cursors being used, just straight
forward selects/joins. The full SP is very long, I have only included the
part that has changed and is making the tempdb grow massively.
The order by is required because I need to find the 1st instance of the
employee history record after the process date.
Eg:
If the process date is 3rd feb and the employee history is:
empid name IsActive fromdate todate
2 tom 0 20th feb 20th march
2 tom 1 1st feb 20th feb
2 tom 0 29th jan 1st feb
2 tom 1 1st jan 29th jan
then i need to find the state of the employee as at 3rd feb. i do this by
finding the first record where the enddate > 3rd feb.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OIqBtGEUGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Well, iterating over 500000 records will take a long time. You don't
> provide the code of your SP; so we don't know how the iteration is taking
> place and make it hard to give you any relevant help; however, using an
> UNION instead of a Cursor for example might be a solution in your case.
> Finally, I really don't understand why you have put an Order By in a
> subquery.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "amy" <amy@.nospam.com> wrote in message
> news:uyBhOeCUGHA.4452@.TK2MSFTNGP12.phx.gbl...
>|||amy
It's hard to suggest without seeing the whole code and understand all
business requirements
I'd start looking at an execution plan , whether or not the optimizer ia
available to use indexes
Aaron has a great article at his web site
http://www.aspfaq.com/show.asp?id=2446
"amy" <amy@.nospam.com> wrote in message
news:O46x64GUGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Thanks for your response. There are no cursors being used, just straight
> forward selects/joins. The full SP is very long, I have only included the
> part that has changed and is making the tempdb grow massively.
> The order by is required because I need to find the 1st instance of the
> employee history record after the process date.
> Eg:
> If the process date is 3rd feb and the employee history is:
> empid name IsActive fromdate todate
> 2 tom 0 20th feb 20th march
> 2 tom 1 1st feb 20th feb
> 2 tom 0 29th jan 1st feb
> 2 tom 1 1st jan 29th jan
> then i need to find the state of the employee as at 3rd feb. i do this by
> finding the first record where the enddate > 3rd feb.
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:OIqBtGEUGHA.5468@.TK2MSFTNGP14.phx.gbl...
>|||[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
amy (amy@.nospam.com) writes:
> The order by is required because I need to find the 1st instance of the
> employee history record after the process date.
> Eg:
> If the process date is 3rd feb and the employee history is:
> empid name IsActive fromdate todate
> 2 tom 0 20th feb 20th march
> 2 tom 1 1st feb 20th feb
> 2 tom 0 29th jan 1st feb
> 2 tom 1 1st jan 29th jan
> then i need to find the state of the employee as at 3rd feb. i do this by
> finding the first record where the enddate > 3rd feb.
The standard idiom is something like:
SELECT eh.issactive
FROM (SELECT * FROM process WHERE processid = @.processid)
CROSS JOIN (employees e
JOIN employeehistory eh
ON e.employessid = eh.empolyeeid
AND e.startdate = (SELECT MAX(eh2.employeedate)
FROM employeehistory eh2
WHERE eh2.empoloyeeid = eh.employessid
AND e.employeedate <= p.processdate)
Unforteunately, this is not going to perform very well. I guess it is
not possible for you change the tables, but for this sort of operation,
it can be far more effecient to have one row per employee and day, even
if it takes up a lot more disk space.
It helps if you include CREATE TABLE and CREATE INDEX statements for your
tables. Also sample data as INSERT statements with sample data is good,
as that helps to test the logic of a query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Amy
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful
DDL and example data. You don't say what should happen if the employee was
only active for part of the period. This will say if the employee was active
at the start of the period or currently active (but not necessarily an
employee at the time of the period) which may be a starting place.
SELECT
e.employeeid
e.name
[EMPLOYEE TABLE] e
JOIN [EMPLOYEE HISTORY TABLE] h ON h.employeeid = e.employeeid AND
h.IsActive = 1 AND ((h.FromDate <= @.FromDate AND h.ToDate >= @.FromDate)
UNION ALL
SELECT
e.employeeid
e.name
[EMPLOYEE TABLE] e
WHERE NOT EXISTS ( SELECT * FROM [EMPLOYEE HISTORY TABLE] h WHERE
h.employeeid = e.employeeid )
AND e.IsActive = 1
John
"amy" wrote:
> Hi, I have an SP that was working fine (took about 10 mins to process).
> I've added a section of the SP below for reference. Here is an overview o
f
> my table structure (simplified).
> EMPLOYEE TABLE
> employeeid
> name
> IsActive
> EMPLOYEE HISTORY TABLE
> employeeid
> name
> IsActive
> FromDate -- this signifies the start date the current employee record info
> was valid.
> ToDate -- this signifies the end date the current employee record info was
> valid.
> PROCESS TABLE
> processid
> name
> startdate
> enddate
> Basically, this is what the SP is meant to do...... when the sp runs, it
> will only carry out processes where the current date is between startdate
> and enddate of the process record. And it should only process employees w
ho
> were active between these dates. I can find out backdated info about an
> employee in the history table. However, if there have been no changes to a
n
> employee (ie: new joiner), then there will be no records in the history
> table so I have to read the current status from the master employee table.
> Hope all this makes sense.
>
> OLD CODE:
> -- this works fairly quickly, but is not correct as it
's
> not looking at historic employee data
> (employees.IsActive = 1)
>
> NEW CODE:
> (
> --if a history record exists for curre
nt
> employee, the read the info
> (
> (select to
p
> 1 IsActive
> from
> employeehistory eh
> where
> eh.todate > process.enddate
> and
> employeeid = employee.employeeid
> order by
> eh.enddate) = 1
> )
> OR
>
> --if no history record exists
> for current employee, then read current employee
> --data in master employee tabl
e
> (
> not exists
> (select *
> from
> employeehistory
> where
> employeeid = employee.employeeid
> )
> AND
> (employees
.IsActive
> = 1)
> )
> )
>
> The changed section has increased the process time massively. Furthermore
,
> it is doing a lot of work on tempdb and hence using up all the harddisk
> space (and eventually fails). The SP is processing approx 500,000 records
.
> Does anyone know why this would happen, and what I can do to improve the
> performace. What is it writing to tempdb? I've tried adding indexes to t
he
> date/employee columns in the history table, but it doesn't help.
>
>
>|||Depending on the circumstances, joining on a sub-query can result in
performance issues.
Instead of doing this:
not exists (select * from employeehistory where employeeid =
employee.employeeid)
Consider doing this:
left join employeehistory on employeehistory.employeeid =
employee.employeeid
Also consider inserting relevent transactions from employeehistory into a
temporary tables and joining with that.
You can use the Display Estimated Execution Plan feature of Query Analyzer
to determine what lookups and indexes are used and compare different
versions of a SQL statement:
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"amy" <amy@.nospam.com> wrote in message
news:uyBhOeCUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hi, I have an SP that was working fine (took about 10 mins to process).
> I've added a section of the SP below for reference. Here is an overview
> of my table structure (simplified).
> EMPLOYEE TABLE
> employeeid
> name
> IsActive
> EMPLOYEE HISTORY TABLE
> employeeid
> name
> IsActive
> FromDate -- this signifies the start date the current employee record info
> was valid.
> ToDate -- this signifies the end date the current employee record info was
> valid.
> PROCESS TABLE
> processid
> name
> startdate
> enddate
> Basically, this is what the SP is meant to do...... when the sp runs, it
> will only carry out processes where the current date is between startdate
> and enddate of the process record. And it should only process employees
> who were active between these dates. I can find out backdated info about
> an employee in the history table. However, if there have been no changes
> to an employee (ie: new joiner), then there will be no records in the
> history table so I have to read the current status from the master
> employee table.
> Hope all this makes sense.
>
> OLD CODE:
> -- this works fairly quickly, but is not correct as
> it's not looking at historic employee data
> (employees.IsActive = 1)
>
> NEW CODE:
> (
> --if a history record exists for
> current employee, the read the info
> (
> (select top
> 1 IsActive
> from
> employeehistory eh
> where
> eh.todate > process.enddate
> and
> employeeid = employee.employeeid
> order by
> eh.enddate) = 1
> )
> OR
>
> --if no history record exists
> for current employee, then read current employee
> --data in master employee table
> (
> not exists
> (select *
> from
> employeehistory
> where
> employeeid = employee.employeeid
> )
> AND
> (employees.IsActive = 1)
> )
> )
>
> The changed section has increased the process time massively.
> Furthermore, it is doing a lot of work on tempdb and hence using up all
> the harddisk space (and eventually fails). The SP is processing approx
> 500,000 records. Does anyone know why this would happen, and what I can do
> to improve the performace. What is it writing to tempdb? I've tried
> adding indexes to the date/employee columns in the history table, but it
> doesn't help.
>
>
Any setting that would reduce locking during a SQL script?
I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
the
occurs.
way
locking?
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
the
occurs.
way
locking?
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
quote:
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
quote:
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
quote:
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
quote:
> that I could make it so that I get table locks instead of row-level
locking?
quote:|||One to look at will be setting the database into single user mode.
> Thanks in advance.
>
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
quote:
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
quote:
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
quote:
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
quote:
> that I could make it so that I get table locks instead of row-level
locking?
quote:
> Thanks in advance.
>
Any setting that would reduce locking during a SQL script?
I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>
Any retired syntax for SQL 2008?
We're in the process of making SQL syntax changes to prepare for an upgrade to SQL 2005, and I don't want to go through this particular exercise again if we can get everything compliant for SQL 2008 while we're making the current changes.
Thank you,
Rob
The BOL from the actual June CTP release contains a list of deprecated keywords and commands.Jens K. Suessmeyer.
http://www.sqlserver2008.de
|||
Hi Rob
It's good to see that you're thinking ahead regarding usage of deprecated features. SQL Server 2005 Books Online topic Deprecated Database Engine Features in SQL Server 2005, section Features Not Supported in the Next Version of SQL Server, already lists all functionality that is expected to be removed in SQL Server 2008 and future releases. I'd recommend that you consider using suggested alternatives for the syntax that has been announced for deprecation while making your syntax changes.
You can also refer to SQL Server 2008 June CTP Books Online topic Discontinued Database Engine Functionality in SQL Server "Katmai" for features that are no longer supported in SQL Server 2008. Please also take a look at topic Deprecated Database Engine Features in SQL Server "Katmai" in SQL Server 2008 June CTP Books Online for Database Engine features that will be removed in the next release after SQL Server 2008. We’ve made a lot of effort in SQL Server 2008 to provide additional support to track usage of deprecated features via deprecation performance counters and trace events. For details, see topic SQL Server, Deprecated Features Object.
Thanks
Sara
|||Thank you very much.
Rob
Any resolution for this error
Error: 0, Severity: 19, State: 0-language_exec: Process 71
generated an access violation. SQL Server is terminating
this process..Any idea what statement was being run by process 71?
--
Denny Cherry
DBA
GameSpy Industries
"Shrikrishna" <srikrishna.s@.ge.com> wrote in message
news:07a901c35bf5$fda01db0$a501280a@.phx.gbl...
> Error: 0, Severity: 19, State: 0-language_exec: Process 71
> generated an access violation. SQL Server is terminating
> this process..
>
generated an access violation. SQL Server is terminating
this process..Any idea what statement was being run by process 71?
--
Denny Cherry
DBA
GameSpy Industries
"Shrikrishna" <srikrishna.s@.ge.com> wrote in message
news:07a901c35bf5$fda01db0$a501280a@.phx.gbl...
> Error: 0, Severity: 19, State: 0-language_exec: Process 71
> generated an access violation. SQL Server is terminating
> this process..
>
2012年2月16日星期四
Any Faster Way To Process This?
Hello All.
I have this script that I need to run every Saturday evening and it takes more than 5 hours to complete. Is there a better way to structure my script? Please advise. Thank you.
update Table_A set financial_yr = t2.Master_financial_year,
financial_period = t2.Master_financial_period
from Master_financial_Table t2
where frst_requested_date >= t2.Start_date and frst_requested_date <= t2.End_date
and financial_year>2000
There are a total of 4.6 million records (and growing weekly) in Table_A and 42 records in Master_financial_table (standard)
Indexes have been created for these tables.Maybe this will be faster
You don't read 4 million times the Master_financial_Table
Declare @.Master_financial_year DateTime
Declare @.Master_financial_period DateTime
Declare @.Start_date DateTime
Declare @.End_date DateTime
Select @.Master_financial_year=Master_financial_year,
@.Master_financial_period=Master_financial_period,
@.Start_date=Start_date,
@.End_date=End_date
From Master_financial_Table
Update Table_A
Set financial_yr=@.Master_financial_year,
financial_period = @.Master_financial_period
Where frst_requested_date between @.Start_date and @.End_date and
financial_year>2000|||I suspect that Table_A will have been given a non-clustered index on frst_requested_date. Given that where frst_requested_date >= t2.Start_date and frst_requested_date <= t2.End_date is going to select a very large number of records, if Table_A has clustered index, you should consider removing it.
In SQL Server 2000, if a table has a clustered index, the leaf nodes of all non-clustered indexes contain the key values of the clustered index corresponding to the index match. So, when a non-clustered index match is found it is followed by a bookmark-lookup on the tables clustered index. On *very* large tables even a highly specific index can return many thousands of records, thereby inducing thousands of clustered index seeks in order to resolve the secondary bookmark lookup. SQL Server should be smart enough to realize a table scan is going to be better, but often it doesnt, and so very large clustered tables exhibit unpleasant performance when a query is conducted through a non-clustered index.
If a table is non-clustered (or heaped), the leaf nodes of all its indexes consist of offset pointers directly into the data blob. This has a significant performance risk in that any index page splitting becomes expensive to fixup on insert or update, but all indexes recover their data by jumping directly to the data row without thousands or millions of bookmark lookups or full blown table scans.
If you can generate a query plan and discover many bookmark lookups are taking place, its worth considering unclustering Table_A.|||If a table is non-clustered (or heaped), the leaf nodes of all its indexes consist of offset pointers directly into the data blob. This has a significant performance risk in that any index page splitting becomes expensive to fixup on insert or update, but all indexes recover their data by jumping directly to the data row without thousands or millions of bookmark lookups or full blown table scans.
I disagree. If the table is a heap the pointers point to the ROWID, which takes into place dbname, table, page, etc. Not sure what you mean by data blob.
and so very large clustered tables exhibit unpleasant performance when a query is conducted through a non-clustered index.
I disagree again. SQL will choose to do a clustered index scan if a table scan is more efficient, a clustered index seek is actually using the index and SQL will usually choose to ignore the NCI and just scan the table (clustered index scan) Also, while you mention pagesplitting, a heap reclaims empty space, this is horrible as it takes time for SQL to find the empty space. Also, choosing the right clustered key will keep your inserts fast and your NCI lookups fast as your key should be small (smaller than the ROWID lookup)
I have found there are very rare occasions when a clustered index should not be on a table.
For the poster - mess with your indexes and play with the "set statistics IO on" command, this will show you how many page reads your query costs you and allow you the ability to tell if removing or adding an index will really benefit you or hurt you.
HTH|||Originally posted by rhigdon
I disagree. If the table is a heap the pointers point to the ROWID, which takes into place dbname, table, page, etc. Not sure what you mean by data blob.
I disagree again. SQL will choose to do a clustered index scan if a table scan is more efficient, a clustered index seek is actually using the index and SQL will usually choose to ignore the NCI and just scan the table (clustered index scan) Also, while you mention pagesplitting, a heap reclaims empty space, this is horrible as it takes time for SQL to find the empty space. Also, choosing the right clustered key will keep your inserts fast and your NCI lookups fast as your key should be small (smaller than the ROWID lookup)
I have found there are very rare occasions when a clustered index should not be on a table.
For the poster - mess with your indexes and play with the "set statistics IO on" command, this will show you how many page reads your query costs you and allow you the ability to tell if removing or adding an index will really benefit you or hurt you.
HTH
You are quite welcome to disagree. However very large data tables behave differently from more modest deployments.
I quote from http://www.sql-server-performance.com/jc_sql_server_quantative_analysis5d.asp
"The key observation for multi-row select queries is that there can be a very wide discrepancy between the point where query optimizer switches the execution plan to a Table Scan and the actually observed cross-over point."
"Other important points include the following. Bookmark Lookups are less expensive for heap organized tables than tables with a clustered index. It is frequently recommended that tables have a clustered index. If clustering only benefits a small fraction of the queries (weighted by the number of rows involved), then it may be better to leave the table a heap."|||"Other important points include the following. Bookmark Lookups are less expensive for heap organized tables than tables with a clustered index. It is frequently recommended that tables have a clustered index. If clustering only benefits a small fraction of the queries (weighted by the number of rows involved), then it may be better to leave the table a heap."
I agree if you will not be having a lot of inserts or deletes. The reclaiming of empty space is non-optimal if either are occuring. The other benefit of more efficient key locks with a clustered index than row locks should be taken into consideration if you will be joining to the table. I would be real interested to hear the posters logical IO when using or not using a clustered index. There are very few tables I work with that are read-only or used solely for querying, the only place I really have that is in a warehouse that I use cubes for anyway rather than SQL to query.
Looks like an interesting article, will have a read.|||Karolyn, limteckboon's query is not going to make four million passes through the table. That's ridiculous. Your solution is functionally equivalent to his, but with extra coding.
limteckboon,
It's unclear whether fields frst_requested_date, and financial_year are in Table_A or Master_financial_Table. It makes a difference in how the query should be written. Please clarify. If they are in Table_A then possibly a JOIN instead of a WHERE clause would improve efficiency:
update Table_A
set financial_yr = t2.Master_financial_year,
financial_period = t2.Master_financial_period
from Table_A
inner join Master_financial_Table t2
on Table_A frst_requested_date between t2.Start_date and t2.End_date
and Table_A.financial_year>2000
In the meantime, you may get better performance if you DROP the indexes on Table_A prior to your update, especially any indexes involving columns financial_yr and financial_period. Add the indexes back in at the end of the process.
blindman|||my query is not equivalent...
there's no JOINs on my query
so the UPDATE query will be excuted faster|||reindexing a 4-million-table must cost a lot...|||It costs less than continuously reshuffling the index pages.|||Hello All.
Thank you very much for all your most valuable advises. Greatly appreciated. Now, I need time to digest them. Will give a try on the 2 suggested codes to see which is more suitable for me but I am very sure both set of codes will definitely give better performance than mine.
Once again Thanks a million to all.
Best regards|||i don't see a difference between Karolyn and blindy's code, except for "extra coding" which actually eliminates a whole table out of the update (that's actually a plus, blindy)
limteckboon:
consider non-conventional data modifications. i guarantee that we can shrink the execution time down to under 60 minutes, maybe even less than 30 if you stop listening to blabbering about indexing and inner joins.
did i get your attention?|||ms_sql_dba posted in another thread
"i've done this type of update on larger number of rows without using update. the trick? use queryout on bcp with values that you want to have, then truncate and bcp data back in."
to his idea
--> use Bulk Insert to put back in the data is faster than Bcp
--> and set the option 'select into/bulkcopy' to True with this command
exec sp_option 'DataBaseName', 'select into/bulkcopy','True'|||simple... *** admiration ***|||If you're going to examine 4 million rows, so a table scan is going to be done (almost certainly), but I doubt that the scanning time is going to be an issue. What will certainly be an issue is the fact that you are updating every single one of them, every single time!
Once you've processed 4 million records this week, how many of them are really going to change by next week? Your query should select, and only update the status of, those records which have changed (or that do not have a known status yet). It seems to me that if you are examining the "first-requested" date, not too many records will ever change status a second time! Take full advantage of that!
If you must make the change to all the records, write a script that processes 1,000 records at a time, then commits. Otherwise, the server is prepared to roll back every one of those changes!
Any change to an indexed column will cause the index to be updated every time. This should be avoided.
Just as an afterthought: can these be calculated fields? I mean, with only twenty-something date ranges, total ...
No matter how efficient a computer or a piece of software may be, the best way to get good performance out of it is to ask it to do absolutely no more than it has to. I think that the root cause of the problem is that you are making the server do far too much work.
I have this script that I need to run every Saturday evening and it takes more than 5 hours to complete. Is there a better way to structure my script? Please advise. Thank you.
update Table_A set financial_yr = t2.Master_financial_year,
financial_period = t2.Master_financial_period
from Master_financial_Table t2
where frst_requested_date >= t2.Start_date and frst_requested_date <= t2.End_date
and financial_year>2000
There are a total of 4.6 million records (and growing weekly) in Table_A and 42 records in Master_financial_table (standard)
Indexes have been created for these tables.Maybe this will be faster
You don't read 4 million times the Master_financial_Table
Declare @.Master_financial_year DateTime
Declare @.Master_financial_period DateTime
Declare @.Start_date DateTime
Declare @.End_date DateTime
Select @.Master_financial_year=Master_financial_year,
@.Master_financial_period=Master_financial_period,
@.Start_date=Start_date,
@.End_date=End_date
From Master_financial_Table
Update Table_A
Set financial_yr=@.Master_financial_year,
financial_period = @.Master_financial_period
Where frst_requested_date between @.Start_date and @.End_date and
financial_year>2000|||I suspect that Table_A will have been given a non-clustered index on frst_requested_date. Given that where frst_requested_date >= t2.Start_date and frst_requested_date <= t2.End_date is going to select a very large number of records, if Table_A has clustered index, you should consider removing it.
In SQL Server 2000, if a table has a clustered index, the leaf nodes of all non-clustered indexes contain the key values of the clustered index corresponding to the index match. So, when a non-clustered index match is found it is followed by a bookmark-lookup on the tables clustered index. On *very* large tables even a highly specific index can return many thousands of records, thereby inducing thousands of clustered index seeks in order to resolve the secondary bookmark lookup. SQL Server should be smart enough to realize a table scan is going to be better, but often it doesnt, and so very large clustered tables exhibit unpleasant performance when a query is conducted through a non-clustered index.
If a table is non-clustered (or heaped), the leaf nodes of all its indexes consist of offset pointers directly into the data blob. This has a significant performance risk in that any index page splitting becomes expensive to fixup on insert or update, but all indexes recover their data by jumping directly to the data row without thousands or millions of bookmark lookups or full blown table scans.
If you can generate a query plan and discover many bookmark lookups are taking place, its worth considering unclustering Table_A.|||If a table is non-clustered (or heaped), the leaf nodes of all its indexes consist of offset pointers directly into the data blob. This has a significant performance risk in that any index page splitting becomes expensive to fixup on insert or update, but all indexes recover their data by jumping directly to the data row without thousands or millions of bookmark lookups or full blown table scans.
I disagree. If the table is a heap the pointers point to the ROWID, which takes into place dbname, table, page, etc. Not sure what you mean by data blob.
and so very large clustered tables exhibit unpleasant performance when a query is conducted through a non-clustered index.
I disagree again. SQL will choose to do a clustered index scan if a table scan is more efficient, a clustered index seek is actually using the index and SQL will usually choose to ignore the NCI and just scan the table (clustered index scan) Also, while you mention pagesplitting, a heap reclaims empty space, this is horrible as it takes time for SQL to find the empty space. Also, choosing the right clustered key will keep your inserts fast and your NCI lookups fast as your key should be small (smaller than the ROWID lookup)
I have found there are very rare occasions when a clustered index should not be on a table.
For the poster - mess with your indexes and play with the "set statistics IO on" command, this will show you how many page reads your query costs you and allow you the ability to tell if removing or adding an index will really benefit you or hurt you.
HTH|||Originally posted by rhigdon
I disagree. If the table is a heap the pointers point to the ROWID, which takes into place dbname, table, page, etc. Not sure what you mean by data blob.
I disagree again. SQL will choose to do a clustered index scan if a table scan is more efficient, a clustered index seek is actually using the index and SQL will usually choose to ignore the NCI and just scan the table (clustered index scan) Also, while you mention pagesplitting, a heap reclaims empty space, this is horrible as it takes time for SQL to find the empty space. Also, choosing the right clustered key will keep your inserts fast and your NCI lookups fast as your key should be small (smaller than the ROWID lookup)
I have found there are very rare occasions when a clustered index should not be on a table.
For the poster - mess with your indexes and play with the "set statistics IO on" command, this will show you how many page reads your query costs you and allow you the ability to tell if removing or adding an index will really benefit you or hurt you.
HTH
You are quite welcome to disagree. However very large data tables behave differently from more modest deployments.
I quote from http://www.sql-server-performance.com/jc_sql_server_quantative_analysis5d.asp
"The key observation for multi-row select queries is that there can be a very wide discrepancy between the point where query optimizer switches the execution plan to a Table Scan and the actually observed cross-over point."
"Other important points include the following. Bookmark Lookups are less expensive for heap organized tables than tables with a clustered index. It is frequently recommended that tables have a clustered index. If clustering only benefits a small fraction of the queries (weighted by the number of rows involved), then it may be better to leave the table a heap."|||"Other important points include the following. Bookmark Lookups are less expensive for heap organized tables than tables with a clustered index. It is frequently recommended that tables have a clustered index. If clustering only benefits a small fraction of the queries (weighted by the number of rows involved), then it may be better to leave the table a heap."
I agree if you will not be having a lot of inserts or deletes. The reclaiming of empty space is non-optimal if either are occuring. The other benefit of more efficient key locks with a clustered index than row locks should be taken into consideration if you will be joining to the table. I would be real interested to hear the posters logical IO when using or not using a clustered index. There are very few tables I work with that are read-only or used solely for querying, the only place I really have that is in a warehouse that I use cubes for anyway rather than SQL to query.
Looks like an interesting article, will have a read.|||Karolyn, limteckboon's query is not going to make four million passes through the table. That's ridiculous. Your solution is functionally equivalent to his, but with extra coding.
limteckboon,
It's unclear whether fields frst_requested_date, and financial_year are in Table_A or Master_financial_Table. It makes a difference in how the query should be written. Please clarify. If they are in Table_A then possibly a JOIN instead of a WHERE clause would improve efficiency:
update Table_A
set financial_yr = t2.Master_financial_year,
financial_period = t2.Master_financial_period
from Table_A
inner join Master_financial_Table t2
on Table_A frst_requested_date between t2.Start_date and t2.End_date
and Table_A.financial_year>2000
In the meantime, you may get better performance if you DROP the indexes on Table_A prior to your update, especially any indexes involving columns financial_yr and financial_period. Add the indexes back in at the end of the process.
blindman|||my query is not equivalent...
there's no JOINs on my query
so the UPDATE query will be excuted faster|||reindexing a 4-million-table must cost a lot...|||It costs less than continuously reshuffling the index pages.|||Hello All.
Thank you very much for all your most valuable advises. Greatly appreciated. Now, I need time to digest them. Will give a try on the 2 suggested codes to see which is more suitable for me but I am very sure both set of codes will definitely give better performance than mine.
Once again Thanks a million to all.
Best regards|||i don't see a difference between Karolyn and blindy's code, except for "extra coding" which actually eliminates a whole table out of the update (that's actually a plus, blindy)
limteckboon:
consider non-conventional data modifications. i guarantee that we can shrink the execution time down to under 60 minutes, maybe even less than 30 if you stop listening to blabbering about indexing and inner joins.
did i get your attention?|||ms_sql_dba posted in another thread
"i've done this type of update on larger number of rows without using update. the trick? use queryout on bcp with values that you want to have, then truncate and bcp data back in."
to his idea
--> use Bulk Insert to put back in the data is faster than Bcp
--> and set the option 'select into/bulkcopy' to True with this command
exec sp_option 'DataBaseName', 'select into/bulkcopy','True'|||simple... *** admiration ***|||If you're going to examine 4 million rows, so a table scan is going to be done (almost certainly), but I doubt that the scanning time is going to be an issue. What will certainly be an issue is the fact that you are updating every single one of them, every single time!
Once you've processed 4 million records this week, how many of them are really going to change by next week? Your query should select, and only update the status of, those records which have changed (or that do not have a known status yet). It seems to me that if you are examining the "first-requested" date, not too many records will ever change status a second time! Take full advantage of that!
If you must make the change to all the records, write a script that processes 1,000 records at a time, then commits. Otherwise, the server is prepared to roll back every one of those changes!
Any change to an indexed column will cause the index to be updated every time. This should be avoided.
Just as an afterthought: can these be calculated fields? I mean, with only twenty-something date ranges, total ...
No matter how efficient a computer or a piece of software may be, the best way to get good performance out of it is to ask it to do absolutely no more than it has to. I think that the root cause of the problem is that you are making the server do far too much work.
2012年2月9日星期四
ANSI Standards
We are in the process of migrating our MS SQL Server 2000 databases to MS
SQL Server 2005.
We have using the MS Upgrade Advisor to flag problems in our databases so
they can be fixed. Does there exist a tool from which we can get a report on
which databases contain components (tables, columns, stored procedures) that
do not meet ANSI standards?You can try Best Practices Analyzer tool for SQL Server 2000 by Microsoft ..
here is the link to download page:
http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
"Loren Z" wrote:
> We are in the process of migrating our MS SQL Server 2000 databases to MS
> SQL Server 2005.
> We have using the MS Upgrade Advisor to flag problems in our databases so
> they can be fixed. Does there exist a tool from which we can get a report on
> which databases contain components (tables, columns, stored procedures) that
> do not meet ANSI standards?
>
>
SQL Server 2005.
We have using the MS Upgrade Advisor to flag problems in our databases so
they can be fixed. Does there exist a tool from which we can get a report on
which databases contain components (tables, columns, stored procedures) that
do not meet ANSI standards?You can try Best Practices Analyzer tool for SQL Server 2000 by Microsoft ..
here is the link to download page:
http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
"Loren Z" wrote:
> We are in the process of migrating our MS SQL Server 2000 databases to MS
> SQL Server 2005.
> We have using the MS Upgrade Advisor to flag problems in our databases so
> they can be fixed. Does there exist a tool from which we can get a report on
> which databases contain components (tables, columns, stored procedures) that
> do not meet ANSI standards?
>
>
ANSI Standards
We are in the process of migrating our MS SQL Server 2000 databases to MS
SQL Server 2005.
We have using the MS Upgrade Advisor to flag problems in our databases so
they can be fixed. Does there exist a tool from which we can get a report on
which databases contain components (tables, columns, stored procedures) that
do not meet ANSI standards?You can try Best Practices Analyzer tool for SQL Server 2000 by Microsoft ..
here is the link to download page:
http://www.microsoft.com/downloads/...&displaylang=en
"Loren Z" wrote:
> We are in the process of migrating our MS SQL Server 2000 databases to MS
> SQL Server 2005.
> We have using the MS Upgrade Advisor to flag problems in our databases so
> they can be fixed. Does there exist a tool from which we can get a report
on
> which databases contain components (tables, columns, stored procedures) th
at
> do not meet ANSI standards?
>
>
SQL Server 2005.
We have using the MS Upgrade Advisor to flag problems in our databases so
they can be fixed. Does there exist a tool from which we can get a report on
which databases contain components (tables, columns, stored procedures) that
do not meet ANSI standards?You can try Best Practices Analyzer tool for SQL Server 2000 by Microsoft ..
here is the link to download page:
http://www.microsoft.com/downloads/...&displaylang=en
"Loren Z" wrote:
> We are in the process of migrating our MS SQL Server 2000 databases to MS
> SQL Server 2005.
> We have using the MS Upgrade Advisor to flag problems in our databases so
> they can be fixed. Does there exist a tool from which we can get a report
on
> which databases contain components (tables, columns, stored procedures) th
at
> do not meet ANSI standards?
>
>
订阅：
博文 (Atom)