Taking Time For Retrieve Data From Temperary Table
Feb 11, 2008View 1 Replies
ADVERTISEMENT
Time Taking Table
Oct 12, 2007Dear Experts,
i've one table named table11. in this perticular table, i've 30 columns and 40,000 rows of data.
this table is taking 35 sec for select * from table11.
defnetly it will take more time if i used this in some places like procedures and functions or views like that.
where is the problem? generally it takes that much of time or is there any problem?
guidence please.....
Vinod
Even you learn 1%, Learn it with 100% confidence.
Table Update Process Is Taking Too Much Time
Mar 9, 2008
hi,
I'm using a OLE DB COMMAND component to perform an update (SQL statement) but the procces takes about 9 hours, so I changed it to a stored procedure but it was the same, I need to update about a a million of rows and the package is very simple.
How can I improve the time, Can I use another component or startegy?
thanks
Not Able To Retrieve Rows From Small Table-So Many Time For So Long
Mar 8, 2005Dear Participants,
We are using merge replication for multi locational database but we are facing one problem in only one table which is not included in replication-
Table name is xxxxmast has only 39 row static information, but it used by every users for all task as select only information from this table like-
Select fin_year into mem_variable from xxxxmast where co_name = :global_variable
go
Code validation from here only.
Right validation from here only.
Report retrival validation from here only.
It means its usage for select from every user frequentely for so many times but we have to only fetch information from this table.
It was working prior fine but rightnow get problem for while-
Today Dated 08-March this table not accessible fro three times in eight hours-
1st time for 10 minute.
2nd time 10 minutes
3rd time 52 minutes.
Users want to login but at the login time years and other validation from this table, so users awaited for above mention time.
We had have do following by yesterday-
Drop table xxxxmast.
Create table xxxxmast.
Insert required data.
This is realy trouble for our application.
Any help realy great for us.
Thanks
R.Mall
Best Way To Retrieve Oracle Data Into SS2000 Near Real-time?
Jul 19, 2007I have no idea where to post this kind of question, so here it is!
I have a requirement to retrieve oracle 10 data into SS2000 in as near real-time as possible (stupid users!) and join with resident SS data for on-demand reporting. (We use SS replication to populate some reporting tables from other SS2000 instances and this has spoiled the users as well as the developers! )
I would like to know if there are any clever ways of doing this, or if a plain-old DTS package running in some kind of loop is the practical answer. 1 minute delay is probably too long . . . I don't know if data can be pushed from the oracle side. Or if we need to write a Service and use it to suck and push.
Any suggestions?
thanks!
Help: Database Table Taking 9GB Of Disk Space But Only Contains 50Mb Data.
Dec 11, 2007Disk space is increasing at an alarming rate (about 500MB a day). Shrink of database seems to have no effect. It is a queueing table so data is inserted then a few minutes later it gets processed & deleted.
Here is info about the problem:
sp_spaceused ImporterModuleQueue
name rows reserved data index_size unused
------------------- ----------- ------------------ ------------------ ------------------ ------------------
ImporterModuleQueue 30 9469432 KB 9468280 KB 32 KB 1120 KB
This is about 9 GB
select sum(len(QueueContent)) from ImporterModuleQueue
49744918
select sum(len(QueueErrors)) from ImporterModuleQueue
43529
This is about 50 Mb
CREATE TABLE [dbo].[ImporterModuleQueue](
[ImporterModuleQueueId] [int] IDENTITY(1,1) NOT NULL,
[ImporterModuleId] [int] NOT NULL,
[StartedDateTime] [datetime] NULL,
[FinishedDateTime] [datetime] NULL,
[QueueContent] [varchar](max) NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[QueueErrors] [varchar](max) NULL,
[QueueSourceId] [int] NOT NULL,
[QueueStatusId] [int] NOT NULL CONSTRAINT [DF_ImporterModuleQueue_IsProcessed] DEFAULT ((0)),
CONSTRAINT [PK_ImporterProcessQueue] PRIMARY KEY NONCLUSTERED
(
[ImporterModuleQueueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--
dbcc opentran
No active open transactions.
--
DBCC SHOWCONTIG scanning 'ImporterModuleQueue' table...
Table: 'ImporterModuleQueue' (2030070418); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 14
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 1.4
- Scan Density [Best Count:Actual Count].......: 20.00% [2:10]
- Extent Scan Fragmentation ...................: 80.00%
- Avg. Bytes Free per Page.....................: 5714.1
- Avg. Page Density (full).....................: 29.40%
How To Let Result Data Place At Temptable Temperary For Other Mapping? Should I Create Temptable Firstly?
Dec 11, 2007Hi!
I have several problems of my coding, please give me some advice.
1. How to let result data place at temptable temperary for other mapping? should i create temptable first ?
2. When I got the duplicated record result, I require to map with the main tables (tblROrder & tblSOrder)to find out the record reference no. Please advice how to handle this issue with reference no. at the outcome.
Many thanks,
New Learner
***Coding as following
SELECT code, SMSNo, holderNo, count(*) From tblROrder
WHERE Day = @Day
GROUP BY code, SMSNo, SholderNo
HAVING COUNT(*) > 1<=====result will be found, but since i didn't get the RefNo at the listing, please advise how to let the outcome with RefNo
INTO #tmpOne (code, SMSNo, holderNo) <====error found
SELECT code, SMSNo, holderNo, GrossAmt, count(*) From tblSOrder
WHERE Day = @prmDay between D1 AND D14
GROUP BY code, SMSNo, holderNo, GrossAmt
HAVING COUNT(*) > 1<=====result will be found, but since i didn't get the RefNo at the listing, please advise how to let the outcome with RefNo
INTO #tmpTwo (code, SMSNo, holderNo) <====error found
TSQL + VBA - Retrieve SQL SERVER 2000 Data Trough Excel 2003 - Time Out Error 80040e31
Sep 17, 2007Hi guys,
When I thought everything is okay with this script, I got a new problem...
I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000.
in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results.
The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR:
run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors.
Thanks in advance,
Aldo.
Below the code:
Code Snippet
Function QuerySalesAging()
'--------------------------------------------------------------
'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library
'--------------------------------------------------------------
Dim ConnString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
'Setting Connection String
Driver = "{SQL Server}"
ServerName = "SERVER"
DB_Name = CompanyName
ConnString = "Driver=" & Driver & ";" & "Server=" & ServerName & ";" _
& "Database=" & DB_Name & ";" & "Uid=" & SQLLoginName & ";" & "Pwd=" & SQLPassword & ";"
'Report Criterias
Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange
' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay.
' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table
TemporaryTableName = "CTE" ' The table is a regular one
CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..."
CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName
CmdLine03 = CmdLine03 & " FROM ..."
CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")"
CmdLine03 = CmdLine03 & " ORDER BY ..."
CmdLine04 = CmdLine04 & " ALTER TABLE " & TemporaryTableName ...
CmdLine05 = CmdLine05 & " UPDATE " & TemporaryTableName ...
CmdLine06 = CmdLine06 & " SELECT ..."
CmdLine06 = CmdLine06 & " FROM ..."
ConnString.Open
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02
RecordSet.Open CmdLine01, ConnString
RecordSet.Open CmdLine02, ConnString
RecordSet.Open CmdLine03, ConnString
RecordSet.Open CmdLine04, ConnString
RecordSet.Open CmdLine05, ConnString
RecordSet.Open CmdLine06, ConnString
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02 ' The debbuger stops here:" if object_id('CTE') is not null exec('DROP TABLE CTE') "
ConnString.Execute CmdLine03
ConnString.Execute CmdLine04
ConnString.Execute CmdLine05
ConnString.Execute CmdLine06
ConnString.Execute CmdLine02
'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next
ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet
'Cleanup & Close ADO objects
ConnString.Execute "USE master"
ConnString.Close
Set RecordSet = Nothing
Set ConnString = Nothing
End Function
Sql Server Is Taking More Time
Jun 11, 2002I have a VB application which uses SQL server as the database and uses Crystal reports for reporting.We are using a stored procedure to create a report and we pass ID from the vb side to run the stored procedure.
In boston the report shows up in 4 sec.But in california it takes 7 min.
We have a very good network(T3).Why it is taking more time in california ?.
Any ideas ?
Query Taking Time
Sep 13, 2001Hi,
I am running this query and it is taking over 3 minutes.
"select * from table1 where CONVERT(varchar(10),dated,5) = '13-09-01' "
Table1 has a column called dated which is datetime datatype.
Any suggestions how can i optimize this query?I tried Non-clustered index on Dated column and time came down to less than 3 but still more than 2min.
TIA.
Time Taking Query
Nov 2, 2007Dear All,
here i'm posting my query which is taking 3 minutes
please suggest me the best query
SELECT distinct INP.COLUMN001 REPORT_INPUT_ID, INP.COLUMN002 REPORT_ID, INP.COLUMN003 OPERATION_ID,
OPER.COLUMN004 OPERATION_CODE, OPER.COLUMN005 OPERATION_NAME, INP.COLUMN004 ITEM_ID,
CONVERT(NVARCHAR , INP.COLUMN005, 110) RECEIVED_DATE, INP.COLUMN006 LOT_NO, INP.COLUMN007 RECEIVED_QTY,
INP.COLUMN008 CONSUMED_QTY, (select CODE from view1 where item_id = INP.COLUMN004) my_val,
(select NAME from view1 where item_id = INP.COLUMN004) Item_Name, INP.COLUMN009 UOM_ID,
U.UOM_CODE, INP.COLUMN010 BASE_RECEIVED_QTY, INP.COLUMN011 BASE_CONSUMED_QTY,
case when INP.COLUMN012 ='1' then 'Progress' when INP.COLUMN012 ='2' then 'Closed' end OPERATION_STATUS,
case when INGDTL.COLUMN006 ='0' then 'Ingredient' when INGDTL.COLUMN006 ='1' then 'Intermediate' end INPUT_TYPE,
INP.COLUMNB01 COLUMNB01, INP.COLUMNB02 COLUMNB02, INP.COLUMNB03 COLUMNB03, INP.COLUMNB04 COLUMNB04,
INP.COLUMNB05 COLUMNB05, INP.COLUMNB06 COLUMNB06, INP.COLUMNB07 COLUMNB07, INP.COLUMNB08 COLUMNB08,
INP.COLUMNB09 COLUMNB09, INP.COLUMNB10 COLUMNB10, INP.COLUMND01 BRANCHID, INP.COLUMND02 COMPANYID, INP.COLUMND03 CREATEDBY,
INP.COLUMND04 CREATEDDATE, INP.COLUMND05 LASTUPDATEDBY, INP.COLUMND06 LASTUPDATEDDATE, INP.COLUMND07 ROWGUID,
INP.COLUMND08 UPDATEDSITE, INP.COLUMND09 LANGID, WC.COLUMN009 WIP_WAREHOUSE_ID,
(SELECT (sum(WIP.COLUMN011) - sum(wip.column010))
FROM TABLE066 WIP where wip.column008 = INP.column004 and WIP.COLUMN005 = '8cd741c7-1ac6-4839-88e7-df85518170f1' and wip.column006 = inp.column003 ) WIP_Qty ,
WIPM.Column005 WIP_ITEM_ID
FROM TABLE073 INP
left join view1 I on I.ITEM_ID = INP.COLUMN004
left join view2 U on U.UOM_ID = INP.COLUMN009
left join TABLE022 OPER ON OPER.COLUMN001 = INP.COLUMN003
left join TABLE066 WIP on WIP.column008 = INP.column004
left join TABLE015 WC on WC.COLUMN001 = OPER.COLUMN008
left JOIN TABLE040 INGDTL ON INGDTL.COLUMN002 = INP.COLUMN004 AND WIP.column008 = INGDTL.COLUMN002
left join TABLE065 WIPM on WIPM.column005 = INP.column004
where INP.COLUMN002 = '057f87aa-7884-43fa-8984-9b74c971da62' order by my_val
thank you very much
Scheduled Job Is Taking More Time. Any Suggestions ?
Jun 3, 2002I have a stored procedure when I run it manually it takes 23 seconds.
I Scheduled a job to run the same stored procedure.It is taking 33 minutes.
Any ideas why it is taking so much time ?
How To Reduce Time In Taking BACKUP
Dec 6, 1999Hi guys.
I am having trouble in time issues while backuping my database.
My database size is around 50GB. It is taking around 5hrs.
Is there any way to reduce the 5 hr backup time to 3 or less.
Thanks in advance
MAK
Inserts Taking Longer Time
May 23, 2007Hello All,
I have SQL Server 2005 installed on my machine and I am firing following query to insert 1500 records into a simple table having just on column.
Declare @i int
Set @i=0
While (@i<1500)
Begin
Insert into test2 values (@i)
Set @i=@i+1
End
Here goes the table definition,
CREATE TABLE [dbo].[test2](
[int] NULL
) ON [PRIMARY]
Now the problem with this is that on one of my server this query is taking just 500ms to run while on my production and other test server this query is taking more than 25 seconds.
Same is the problem with updates. I have checked the configurations of both the servers and found them to be the same. Also there are no indexes defined on either of the tables. I was wondering what can be the possible reason for this to happen. If any of u people has any pointers regarding this, will be really useful
Thanks in advance,
Mitesh
ExecuteQuery Taking A Long Time
Sep 4, 2007Hi:
I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.
How can expediate my query execution process.
Environment details
Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.
Connect String in java code:
jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd
and use PreparedStatement and ResultSet.
Regards
Arup
Dm Query Taking Long Time
May 16, 2007I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.
use MyDatabase
GO
select db_name(database_id) as 'Database', o.name as 'Table',
s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',
avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,
page_count, avg_page_space_used_in_percent, record_count,
ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,
schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s
join sys.objects o on s.object_id = o.object_id
join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where db_name(database_id) = 'MyDatabase'
order by avg_fragmentation_in_percent desc
--order by avg_fragment_size_in_pages desc
--order by page_count desc
--order by record_count desc
--order by avg_record_size_in_bytes desc
SSIS Package Taking Much Time Than DTS
Apr 14, 2008
Hi,
I have a DTS in SQL Server 2000, Where I am importing some data from a remote server (SQL 2000) to local server (SQL 2000).And this is working fine. it is taking max of 1 min to execute the package.
Now I have created the same DTS in sql server 2005 (SSIS) where the source server is sql server 2000 and the destination server is 2005.and I have created the ssis in that server. The same logic which i have created in sql 2000. But here it is taking almost 10 min to execute the package.
Where as the same in sql server 2000 taking max of 1 min. Why this happening.. Is there any configuration to execute the SSIS package.?
Fuzzy Lookup Taking Too Much Time
Dec 14, 2006I have a SSIS package where a small table of 270 rows are fuzzy looked up with a table in another sql server and inserts the records to a temporary table. This takes more than 3 hours in debug mode or so and never goes beyond this step.I have used a OLE DB destination to insert to temporary table and temporary table doesn't get a value.
View 2 Replies View RelatedRestore Taking Very Long Time
May 31, 2007i have sql 2000 db of about 120 GB. its taking about 10 -12 hours to restore on the same disk as new database.
server configuration is good.
when i try to restore another db of about 10 GB size, its restoring in about 5 minutes.
How Can I Retrieve Data From One Table To Another
Jan 12, 2006Hi, everione,
I am having a big problem, trying to create datebase.
I have 3 tables: SUPERAVATAR, MASTERAVATAR, MEGAAVATAR.
- SuperAvatars are heroes in an online role playing gaming. They have an ID, ‘superavatarID’ which contain an UNIQUE NUMBER NOT NULL PRIMARY KEY to identify them.
A wisdom – ‘trickster’,’conjuror’,’magician’, etc..
A current owner… who is the user or player of the game and has that Super Avatar– we associate the link to USERID to know the person.
SuperAvatars can be fathers or mothers of Mega Avatars.
-MegaAvatars are the children of SuperAvatars…. They also have an ID UNIQUE NUMBER NOT NULL PRIMARY KEY to indentify them.
A magic power – it can be a ‘Leader’ or ‘Sheep’…
A ‘parent’ – parent is the number identifying to know to who Super Avatar belongs.
e.g.
SUPERAVAT WISD CURRENTOWNER FATHEROF MOTHEROF
1 Thick 3 1
2 Mentally Ch. 11 3
3 Smart 9 2
4 Genius 16 4
5 Thick 19
MEGAAVATARID MAGICPOWER PARENT
1 MAGICIAN 1
2 WIZARD 3
3 SORCERER 2
4 MAGICIAN 4
-We see that MEGAAVATAR 1 has a magic power as Magician and his father is ‘1’. ‘1’ identifies the SUPERAVATAR.
We see SUPERAVATARID…. who has the number ‘1’? the first in the row… who has wisdom – thick and he belongs to the USER with ID number 3.
-We see MEGAAVATARID… we choose the number 2…. His magic power is as WIZARD… and his father is the number 3.
We see SUPERAVATARID now… we look up the SuperavatarID 3…. We can see he has a wisdom – GENIUS… who belongs to the USERID 16 and he is father of MEGAAVATAR number 2.
The list can carry on and never stop.
I have this Problems:
We create in this example 3 tables: Users, SuperAvatar, MegaAvatar
SQL
CREATE TABLE users(userID NUMBER CONSTRAINT pk_user PRIMARY KEY,email VARCHAR2(50) NOT NULL UNIQUE,password VARCHAR2(15) NOT NULL UNIQUE,subscription CHAR(8) NOT NULL CHECK (subscription IN('ACTIVE' , 'INACTIVE' ) ) );
CREATE TABLE superavatar(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL, fatherOf NUMBER,motherOf NUMBER);
CREATE TABLE megaavatar (megaavatarID NUMBER CONSTRAINT pk_megaavatar PRIMARY KEY, magicPower VARCHAR2(12) NOT NULL CHECK (magicPower IN('TRICKSTER','CONJUROR','MAGICIAN','WIZARD','SORCERER')), parent NUMBER);
Now, the 3 tables are created…..
What happen when we try to insert values to this table?
In this case we insert to User Table some examples:
INSERT INTO users VALUES('3','john@hotmail.com','great78','ACTIVE');
INSERT INTO users VALUES('9','chrisandsandra@gmail.com','chrisandra)','ACTIVE');
Now, we insert to SuperAvatar some examples;
INSERT INTO superavatar VALUES('1','THICK','3','1','');
INSERT INTO superavatar VALUES('3','SMART','9','3','');
|
|
‘9’ is the UserID that we already insert to the User table
What’s the problem?
We have to insert manually the data from USERID to CURRENTOWNER as we didn’t match or link CURRENTOWNER from SUPERAVATAR Table to USERID from USER Table with a SQL CODE.
What happen if we have thousands of USERS that they register to this game…? We will never know to how belongs that SUPERAVATAR but if someone do it manually can spend a year.
I am trying to fix this problem …. I add in SUPERAVATAR TABLE ‘CHECK’ in currentOwner..
SQL> CREATE TABLE superavatar
(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')),
currentOwner NUMBER NOT NULL CHECK (currentOwner IN(SELECT userID FROM users)),
fatherOf NUMBER,
motherOf NUMBER);
*
ERROR:
ORA-02251: subquery not allowed here
It doesn’t work.
Please HELP, I have exam tomorrow
thank you
Desy
MSSQLSeverOLAPServieces Is Taking A Long Time To Start
Oct 13, 2003Sometime is necessary to stop MSSQLSeverOLAPServieces to do a full backup in my OLAP Server disks. After backup had finished and I tried to star MSSQLSeverOLAPServieces but it takes almost 30 minutes to the services starts.
What can it be causing that?
Paulo
Stored Proc Taking A Very Long Time
Aug 2, 1999I have a stored procedure that normally takes about 5 hours to complete:
DELETE tblX WHERE PROC_DT < dateadd(day, -93 , getdate())
tblX has about 55 million records and has an index on PROC_DT.
I have this running as a scheduled task. Over the weekend, the task executed and it is still running 56+ hours later. Does anybody have any ideas as to where I should look for the problem? I am afraid to kill the process because of the rollback time.
Update Taking Long Time In 2000 Then SQL 7.0
Mar 7, 2002Hi,
I have a table with 48 million rows,when i executed following update query it is taking 10 HOURS in SQL SERVER 2000 with SP1.
Where as when i executed same query in SQL SERVER7.0 with same table then it is taking 13 MINUTES. Comming to Machine...SQL 2000 Server has more processors and greater memory than SQL 7.0 m/c.
It looks strange but this is true.Does any one faced such problem..is there any bug in SQL 2000?????
Here is Query::
update cus_pay_jan_dist set univ_regdate = b.dayid
from cus_pay_jan_dist a with (nolock), tm_dayids b with (nolock)
where a.univ_regdate = b.dayidnum and a.univ_regdate like '2001%'
Thanks
Ananth
Stored Procedure Taking More Time Than Sql Query
Oct 6, 2005Hi
I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.
What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.
Thanks.
Taking Too Long Time For Insert Statement
Sep 19, 2007Hi All,
Scenario:
There are two applications running on different server say ServerA and ServerB. Both applications are using same database server SQL Server 2005 say ServerB. Called the application as ApplicationA and ApplicationB with respect to Server names
It means for ServerA the database is remote and for ServerB, database is local.
Both the applications are Java application and using datasource to connect to the database. The driver used are SQL Server 2000 driver (which includes 3 jars). This can be a question that why 2000 driver is used for 2005. The reason is, application on ServerA is getting error while using SQL Server 2005 as Driver not proper.
Problem Area:
When ApplicationB (local to database) is doing some DB operations (which includes select and then batch insert), ApplicationA (remote) is trying to insert a record which is taking too long time (around 40 sec.). This is causing timed out in ApplicationA.
ApplicationA is inserting the data into the same table from where ApplicationB is selecting the data.
Any help????
Cheers
Nitin
Dtsx Package Taking Too Much Time To Validate
Dec 11, 2007Does the validation time of DTSX package depend on the amount of data?
I have a data transfer task which contains a oledb source data from a SQL Server 2005 view. Then the data go through 3 Lookup transfromations before going into another view, also on SQL Server 2005 but a different database.
The purpose of this package is to load fact data, so it has to deal with few million of rows. Before setting DelayValidation to true, it takes few minutes just to open the package in BI Studio. Now with DelayValidation set to false, I can open the package without any problem. But it takes more than 5 minutes during Validation, Prepare for Execution and Pre-execution Phase. During the time the memory usage and cpu time on SQL Server goes up significantly. The CPU doesn't hit 100% though. My client machine doesn't have any significant activity.
I have similar packages ( Oledb view -> 3 or 4 lookup Transformations -> Oledb Table) but dealing with dimension data. With DelayValidaion set to False, those packages can be opened in BI Studio within few seconds. They also take only few seconds during those 3 phases before starting actual execution phase.
So I have an impression that the validation time depends on the amount of data in the database. Shouldn't it just depends on the metadata?
Update Statment Taking Long Time
Mar 27, 2008
I have an update statment in my SSIS that use to take 10 minutes in SQL 2000 dts and now its take 1 hour 15 minutes in SQL 2005.
this is my sql update statment -
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null
It is using ole db connection. About 36,000 records that it is updating.
I have read ole db can be slow and to use staging table. Does that mean on all updates like this I have to use a staging table and then insert. I didn't use to have to do this in SQL 2000. Has it changed. Are there any other options?
any input greatly appreciated.
Transaction Log Backup Taking A Long Time
Mar 30, 2007Hello,
I'm trying to figure out why my transaction log backup is taking up to an hour to complete. I started off with a full recovery model with a Full database back up every Sunday, differential backups every Tuesday/Thursday and log backups every 5 minutes. I would have thought that the log file backups would execute much quicker because I'm backing them up more often.
Here is my backup statement, I'm hoping I've got a wrong option that you can point out to me:
BACKUP LOG [xxxx] TO [LogFilexxxxBackups] WITH NOINIT , NOUNLOAD , NAME = N'xxxx log backup', SKIP , STATS = 10, NOFORMAT
Bulk Insert Taking Long Time To Run
Apr 30, 2008
The process is as follow,
The destination table is truncated and indexes are dropped before loading and after data being inserted we re-create the indexes.
Before this, a view extracts data from more than 22 tables from a staging database and tries to insert this data in the destination table.
it used to take 12-15 mins, but since yesterday loading one particular table never completes. While loading, the database is set to Simple recovery. There are no blocking. It's part of a daily batch thats loads 6 GB of data everyday. But while loading on particular table it's just keep running for hours. I tried rebuilding the indexes and re-starting the SQL Server but of no use.
Any help is much appreciated as this production batch job.
Thanks in advance.
To Retrieve Image Data From A Table
Jul 25, 2001Hi,
Can any one help me to retrieve an image data from a table.
I wanted to show the images on my web page using ASP.
I am trying with pub_info table of pubs database, in this table logo is a image data type colum. I opened a record set in ASP page and I am not able to retrieve the image from the record set.
Any help is appreciated!
Ravi.
Retrieve Data From Another Table In Subquery
Apr 11, 2004Hi there, here is my current query:
sqlTemp = "SELECT id,code,description FROM tbl_content " &_
"WHERE EXISTS " &_
"(SELECT * FROM tbl_published_content " &_
"WHERE tbl_content.id = tbl_published_content.id ORDER BY tbl_published_content.nCorrectOrder)"
I don't get results sorted by tbl_published_content.nCorrectOrder, and have also no iea how to retrieve that values.
Please can any one tell me how can I have access to tbl_published_content.nCorrectOrder and get it sorted by this field?
So I can use objRS.("tbl_published_content.nCorrectOrder") or some alias?
thanks a million!
Retrieve Data Into 3 Column Table
Feb 3, 2014What would be the most efficient method of constructing a sql statement to retrieve certain record into (this gets tricky) rows of a table with 3 columns date.desc.
<table>
@foreach(var row in maytable){
<tr>
<td>@row.Most_Recent_Record1</td>
<td>@row.Most_Recent_Record2</</td>
<td>@row.Most_Recent_Record3</</td>
</tr>
}
</table>
How To Retrieve Data From A Second Table With No Foreign Key
Oct 5, 2007
I have 4 tables I am pulling data from. Table A and B can be joined and Table C and D can be joined via foreign keys but A and/or B can not be Joined to C and/or D via foreign Key.
My statement basically looks like this to start
SELECT s.HTENANT, s.HPROP, s.HUNIT, s.SHEADNAME3B, s.DTOT7I
FROM H8summ S join tenant t on s.htenant = t.hmyperson
And returns this :
29226 128 20577 Almukhtar, Khadijah M 4242.00
26574 128 17980 Archie, Mary 20218.00
The last number is the key it is the income.
In another table we have an income limit table setup that would have the low, medium and high settings for the range.
For instance it may have 11400 as the elimit attribute and say 22000 as the vlimit attribute.
Then in the fourth table we the description that can be linked back to the income limit table via the hinclimit attribute. In this case the desc attribute would be ELI for elimit and VLI for vlimit.
So I want to take the income compare it to the income limit table and then return the description
The output would look like this :
29226 128 20577 Almukhtar, Khadijah M 4242.00 eli
26574 128 17980 Archie, Mary 20218.00 vli
I just not sure how to reference the income limit table without the foreign key in the from statement. When I just add it it returns 6 rows for each person (number of rows on the income limit table)