Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Query Taking Too Long


Below is my query which is taking a long time to execute, DB is SQL Server 2005 through a web Application
I have downloaded the latest MS SQL 2005 driver 1.xxx and still the query takes long to execute

The Description field is a Full_text indexed catalog column
the p.vendornumber is a primary key same with c.ID

Any one have an idea why it is taking this long to run

The Execution Time is: 13640 ms Which I think is very long

SELECT Upper(p.Type) Type,p.Modelname,p.partno,Upper(p.description) description,
Upper(p.classification)classification,p.vendornumber,p.mfg,
p.price,c.CompanyName,c.City,c.State,p.thumbnail
FROM P_all p, Acts c
WHERE p.vendornumber = c.ID
AND CONTAINS(p.Description, '"helmet*"')
Order by p.VendorNumber

Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
SQL Query Taking Too Long To Process
dear guys. i have this one problem, where the sql statements really took very long time to be processed. It took more than 1 minute, depending on the total data in the table. I guest this have to do with the 'count' statements. here is the code:

------------------------------------------------------------
$sql = "SELECT company,theID,abbs,A as Active,N as Nonactive,(A+N) as Total
FROM(
select distinct D.nama As company, C.domID As theID, D.abbrew As abbs,
count(distinct case when B.ids is NOT NULL THEN A.dauserid END) As A,
count(distinct case when B.ids is NULL THEN A.dauserid END) As N
FROM
tableuser A LEFT OUTER JOIN tabletranscript B on (A.dauserid=B.dauserid)
INNER JOIN thedommember C ON(C.entitybuktiID=1 AND C.mypriority=1 AND

C.entitybuktiID=A.dauserid)
INNER JOIN mydomain D ON (C.domID=".$getID.")
GROUP BY D.nama, C.domID, D.abbrew
ORDER BY company
)";


Hope any of you can simplify this statements into a query that doesnt take ages to be processed.

Thanks in advance....

View Replies !   View Related
Dm Query Taking Long Time
I'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

View Replies !   View Related
What Is Taking So Long?
Hello.

I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 1595 ms.



SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC



|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)





I have tried to rewrite the query to an INNER JOIN instead.


|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
|--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)


but the query still takes 1,5 seconds.


It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.


How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?

The index I have on the table is a clustered index on (column 2).

Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.

If I remove some columns from the SELECT-list the query will execute alot faster:

SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 32 ms.

Booth the CPU and the elapsed time goes down and now appears to be more normal.

So there seems to be a problem caused by data transfer.
I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?

Any ideas?

I am running Microsoft SQL Server 2000 - 8.00.2039

View Replies !   View Related
What Is Taking So Long?
Hello.

I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.

SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 1595 ms.





Code Snippet

SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC





Code Snippet

|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
     |--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)



 
I have tried to rewrite the query to an INNER JOIN instead.





Code Snippet

|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
     |--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
          |--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
          |--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)

but the query still takes 1,5 seconds.


It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.


How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?

The index I have on the table is a clustered index on (column 2).
 
Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.

If I remove some columns from the SELECT-list the query will execute alot faster:

SQL Server Execution Times:
   CPU time = 32 ms,  elapsed time = 32 ms.

Booth the CPU and the elapsed time goes down and now appears to be more normal.

So there seems to be a problem caused by data transfer.
I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?

Any ideas?

I am running Microsoft SQL Server  2000 - 8.00.2039

View Replies !   View Related
Restore Taking Very Long
im rstoring a db the file is 7gig. Its taking more than 10 minuts..
how do i know if the backup file is ok to restore it

=============================
http://www.sqlserverstudy.com

View Replies !   View Related
SQL Backup Taking Too Long!!!
Hi All

I am having a serious problem which I need some help with regarding our SQL Server backup.

Basically it has started to take ages (as in 48hrs +), when it should only take about 4 hrs. The database is only 380GB and up until monday our backups have not been completing. When I check the activity monitor I have seen that the 'BACKUP DATABASE' process is set to suspended with a huge wait time and the wait type is ASYNC_IO_COMPLETION.

I am not sure how to solve this, but I am going to have to!

So if anyone has any ideas please help me! If you need any othe info please let me know.

Thanks

Gopher

View Replies !   View Related
ADD CONSTRAINT TAKING LONG TI
Hi:

I have issued the following ALTER TABLE CHECK ADD CONSTRAINT on a table which has around 100K rows and it is taking long time (it's been more than 30 mins the alter table is running) to add the constraint. Is this normal or should I kill the process.

ALTER TABLE [dbo].[tblAbsHeqAnalyticOutputSimulationPathValues]
WITH CHECK ADD CONSTRAINT [CK_tblAbsHeqAnalyticOutputSimulationPathValues_1]
CHECK ([dbo].[svfConstraintVerifyTableUniqueActiveEntryFacade]('tblAbsHeqAnalyticOutputSimulationPathValues')<=(1) AND [dbo].[svfConstraintVerifyTableUniqueActiveEntryFacade]('tblAbsHeqAnalyticOutputSimulationPathValues')>=(0))



Thanks !

View Replies !   View Related
Taking To Long Process
To all,

 

Im migrating data but its taking too long to commit insertion, what sould i do?

 

merwinp

View Replies !   View Related
UDF Taking Too Long To Run Workaround ?
I have written a UDF into which I pass a table name, field name, value of the field, whether alpha characters are valid, whether numerics are valid, and a string of alphanumerics that are valid. I return back a string with all invalid characters removed. Unfortunately when I use this on names and addresses in an 12000 row table, it takes forever to run. Can anyone think of an easy way to do this which isn't so labour intensive. Please see code below.

NB CHAR(32) is space, CHAR(45) is -,CHAR(39) is '

CREATE FUNCTION dbo.UDF_RemoveInvalidCharacters
(  @sTableName varchar(50),-- e.g. 'Contact'
  @sFieldname varchar(50),-- e.g. 'Lastname'
 @sFieldValue  varchar(500),-- e.g. 'Jeremi@h O''Grady84'
  @sAlphaValid char(1),-- e.g. 'Y'
  @sNumericValid char(1),--e.g. 'N'
  @sAlphanumericsValid varchar(500))--'CHAR(32):CHAR(45):CHAR(39)'
RETURNS varchar(500)
AS
BEGIN
 DECLARE @sReturnValue   varchar(500),
   @nTableID   int,
   @nFieldLength   int,
  @nCurrentPos   int,
  @sTestChar   char(1),
  @sValid   char(1),
  @nAlphanumericPos  int,
  @sAlphanumericTest  varchar(8),
  @sTempTestChar   varchar(8),
  @sAlphasFound  char(1),
  @sNumericsFound char(1),
  @sAlphanumericsFound char(1)

 --Get ID of table that the field is on
 SELECT  @nTableID = [id]
 FROM  SYSOBJECTS
 WHERE  [name] = @sTableName

 --Get the length of the field
 SELECT  @nFieldLength = sc.length
 FROM  SYSOBJECTS so, SYSCOLUMNS sc
 WHERE  so.id = @nTableID
 AND  sc.id = @nTableID
 AND  sc.name = @sFieldName

 --Initialise values
 SET @sReturnValue = ''
 SET @nCurrentPos = 1
 SET @sValid = 'N'
 SET @sAlphasFound = 'N'
 SET @sNumericsFound = 'N'
 SET @sAlphanumericsFound = 'N'

 --Test each character to ensure it is valid before adding it to the return string, a string consisting solely of alphanumeric characters would be wrong
 WHILE @nFieldLength >= @nCurrentPos
 BEGIN
  SET @sTestChar = substring(@sFieldValue,@nCurrentPos,1)
  IF @sAlphaValid = 'Y' --alphas are valid
  BEGIN
   IF UPPER(@sTestChar) in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
   BEGIN
    SET @sValid = 'Y'
    SET @sAlphasFound = 'Y'
   END
    
  END
  IF @sNumericValid = 'Y' AND @sValid <> 'Y'--numerics are valid
  BEGIN
   IF @sTestChar in ('0','1','2','3','4','5','6','7','8','9')
   BEGIN
    SET @sValid = 'Y'
    SET @sNumericsFound = 'Y'
   END
  END
  SET @nAlphanumericPos = 1
  WHILE LEN(@sAlphanumericsValid) > @nAlphanumericPos AND @sValid <> 'Y' --alphanumerics that are valid
  BEGIN
   IF CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid))) > 0
   BEGIN
    SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid)))-1)
   END ELSE
   BEGIN
    SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,(LEN(@sAlphanumericsValid)-@nAlphanumericPos)+1)
   END
   SET @sTempTestChar = 'CHAR(' + RTRIM(LTRIM(STR(ASCII(@sTestChar)))) + ')'
   IF @sTempTestChar = @sAlphanumericTest AND (@sAlphasFound = 'Y' OR @sNumericsFound = 'Y') --alphanumerics are only valid once we have alpha or numerics
   BEGIN
    SET @sValid = 'Y'
    SET @sAlphanumericsFound = 'Y'
   END
   SET @nAlphanumericPos = @nAlphanumericPos + LEN(@sAlphanumericTest) + 1
  END
  IF @sValid = 'Y'
  BEGIN
   SELECT @sReturnValue = @sReturnValue + @sTestChar
  END
  SET @nCurrentPos = @nCurrentPos + 1
  SELECT @sValid = 'N'
 END
 IF @sAlphanumericsFound = 'Y' AND @sNumericsFound = 'N'  AND @sAlphasFound = 'N' --alphanumerics on their own are not valid
 BEGIN
  SELECT @sReturnValue = ''
 END
 RETURN @sReturnValue --in the example I would get Jeremih O'Grady

View Replies !   View Related
RESTORE Taking A Long Time
Please copy all replies to bains2@slb.com

Each month, we roll a backup of our production database over our development and test environments. Our database is in the 10-15 GB range. Each environment is on a separate server. In 65, this operation could be accomplished in under an hour by restoring a "disk" backup on server A to a database on server B.

With SQL 70, this has run for over 3 hours. The backup itself didn't take that long. If anyone has any idea of what may be happening, or if there are other factors in 70 that I need to consider, please let me know. Below is my SQL code:

restore database QueryDev
from disk = 'amopquerysqlbackupqueryprodqueryprod_db_1999081 82001.bak'
WITH MOVE 'queryprod_data' TO 'd:sqldataquerydev_data.mdf',
MOVE 'queryprod_data2' TO 'd:sqldataquerydev_data2.ndf',
MOVE 'queryprod_data3' TO 'd:sqldataquerydev_data3.ndf',
MOVE 'queryprod_log' TO 'd:sqldataquerydev_log.ldf',
REPLACE,
STATS = 10


Thanks, Buddy

View Replies !   View Related
Stored Proc Taking Too Long
Dear All

I have a stored proc that is taking way too long to process, over 50 minutes.

What I need to do is go through 180K rows, and if the projId and langid is the same, increment 1 to a value, if not, reset the incrementer

I am doing the following at the moment

DECLARE @Id int, @ProjectId int, @LangCode char(6)
DECLARE @CurProjectId int, @CurLangCode char(6)
DECLARE @incrementer int
DECLARE Order_cursor CURSOR FOR

SELECT Id, langCode, projectid FROM #tempTable

OPEN Order_cursor
SET @incrementer = 1
FETCH NEXT FROM Order_cursor INTO @Id, @LangCode, @ProjectId
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@CurProjectId = @ProjectId)
AND (@CurLangCode = @LangCode)
BEGIN
SET @incrementer = @incrementer + 1
END
ELSE
BEGIN
SET @incrementer = 1
END
UPDATE #tempTable
SET edbOrder = @incrementer
WHERE Id = @id
SET @CurProjectId = @ProjectId
SET @CurLangCode = @LangCode
FETCH NEXT FROM Order_cursor INTO @Id, @LangCode, @ProjectId
END
CLOSE Order_cursor
DEALLOCATE Order_cursor


Is there a better way?

Thanks

Johann

View Replies !   View Related
Stored Procedure Taking Too Long
I have a stored procedure that is taking too long to complete. I'ved narrowed the problem down to the following code. Is there any reason why this should take long to complete for about 40,000 records?

UPDATE Entries

SET EntryStatus = 1, reason = 'code entered more than once'

WHERE TimeSubmitted > @StartOfPeriod AND TimeSubmitted < @EndOfPeriod

AND TimeSubmitted <> @t and ShortCode = @ShortCode

AND verbatim = @v

View Replies !   View Related
CONTAINS And WHERE Clause Combination Taking Too Long
Hi,

I have a table with 3 columns and 20 million records.
first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000).
I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column.
Now when i try to search

SELECT

TOP 20

col1,
col3
FROM

tbl
WHERE

col1 = '1234'
AND

CONTAINS(col3,'"market*"')

I am facing following problems
1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second.
2- Some time it show criteria is too complex, although i am only requesting a single word in col3.

I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information.

Please assist.

View Replies !   View Related
Restore Taking Very Long Time
i 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.

View Replies !   View Related
ExecuteQuery Taking A Long Time
Hi:

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

View Replies !   View Related
Stored Proc Taking A Very Long Time
I 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.

View Replies !   View Related
Update Taking Long Time In 2000 Then SQL 7.0
Hi,
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

View Replies !   View Related
MSSQLSeverOLAPServieces Is Taking A Long Time To Start
Sometime 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

View Replies !   View Related
Loading Data Taking A Long Time
I would like your opinion on how to speed up the database loading for a SQL Server Application. The application is designed
to allow smaller sites to load their data into Access 97 databases and larger sites to load data into SQL Server 7.0. The data
is coming from a mainframe legacy database. It is coming to both databases by tcpip and a product named DB2Connect.
I am shocked by how much faster the Access database loads the same amount of data. Time differences are given in the note
below. The tables loaded will have several thousand rows and will be identical on both Access and Sql Server. Access is not
local to the user, it is on a network drive so that is not a possible reason for the speed.
From attending a recent SQL Server class, some of my ideas were to use fixed memory on SQL Server and turn the update
statistics off during loading. Neither change made any difference. What about row level locking and primary keys, is this
causing overhead? Should primary keys be applied after the load? Is this even possible?

A description by a programmer for the application follows:

The numbers are very close to 22 minutes on SQL Server and 1.5 minutes on Access. Same amount of data.
For Access, Jet Engine 3.51 and ADO 2.1 are being used.
For SQL Server, "OLEDB Provider for SQL Server" is being used.
Visual basic is using the "Find" method on the "Recordsets" of data that were populated using SQL.
The Find method is a way of searching through data returned from a database and is known to be a bit slow,
but if it were causing the problem, I would expect to see the slowness on Access as well. All data inserts are done
using recordsets(AddNew and Update calls) instead of SQL because the database can't be updated until everything
is complete and this keeps all the data in local memory.

Any ideas would be appreciated.

View Replies !   View Related
Update Statment Taking Long Time
 

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.
 
 
 

View Replies !   View Related
Checkdb With Repair Taking Long Time
Hi,
 
We had two transaction log files in our database.  One of them got deleted yesterday night.  My database went in suspect.  I did the following:
 
1.  set the database into emergency mode
2.  set the database into single user
3.  DBCC CHECKDB(dbname, REPAIR_ALLOW_DATA_LOSS)
 
The checkdb command is still running since last 9 hours.  I checked the status by using foll. query:
 
SELECT
      s.session_id
      ,r.percent_complete
      ,r.command
FROM
      sys.dm_exec_sessions s join
      sys.dm_exec_requests r on s.session_id = r.session_idsession_id
WHERE
s.is_user_process = 1
 
The output is as follows:
session_id percent_complete command

---------- ---------------- ----------------

55 0 SELECT

59 57.59162 DBCC TABLE CHECK
 
 

View Replies !   View Related
Taking Too Long Time For Insert Statement
Hi 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

View Replies !   View Related
Transaction Log Backup Taking A Long Time
Hello,

 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

View Replies !   View Related
Forced Failover Taking Too Long ... Am I Missing Something?
Hi all,

We have to support forced failover under certain scenarios. Whilst using 'ForceFailoverAndAllowDataLoss' work perfectly the database is not available for several minutes. The failover command returns very quickly, but the database enters a recovery/restore state and will not accept connections for up to 2 minutes.

Am I missing an additional step that would speed this up?

Many thanks,

Nick

View Replies !   View Related
Bulk Insert Taking Long Time To Run
 

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.

View Replies !   View Related
Question Regarding A View That Is Taking Long Time To Process
Good afternoon everyone, I have written a view that pulls customer demographic  infomration as well as pulling data from multiple scalar-valued functions.  I am using this view to pull and send data from one database to another in the same SQL server.  The problem that I am having is that I am running this import as a scheduled job in windows.  The job is taking almost 24 hours to complete this task.  The total number of records that are being pulled is around 21,000+.  I have tried removing the functions from the view and it only takes the view 20 seconds to pull the demographic information from the same 21,000+ records but when I add the function calls this is where the time to complete goes through the roof.  Has anyone encountered this before if so what would you suggest doing?  Any help would be appreciated.  Here is the syntax for my view: SELECT TOP 100 PERCENT CUS_EMAIL AS Email, CUS_CUSTNUM AS MemberID, CUS_PREFIX AS Prefix, CUS_FNAME AS FirstName,
CUS_LNAME AS LastName, CUS_SUFFIX AS Suffix, CUS_TITLE AS Title, CUS_STATE AS State, CUS_COUNTRY AS Country, CUS_ZIP AS ZipCode,
CUS_SEX AS Gender, CAST(CUS_DEMCODEA AS nvarchar(20)) + ',' + CAST(CUS_DEMCODEB AS nvarchar(20))
+ ',' + CAST(CUS_DEMCODEC AS nvarchar(20)) + ',' + CAST(CUS_DEMCODED AS nvarchar(20)) AS DemoCodes,
dbo.GetSubScribedDateMLA(CUS_CUSTNUM, CUS_EMAIL) AS MLASubscribedDate, dbo.GetSubScribedDateMLP(CUS_CUSTNUM, CUS_EMAIL)
AS MLPSubscribedDate, dbo.GetSubScribedDateLDC(CUS_CUSTNUM, CUS_EMAIL) AS LDCSubscribedDate, dbo.GetMLAExpiration(CUS_CUSTNUM,
CUS_EMAIL) AS MLASubExpireDate, dbo.GetMLPExpiration(CUS_CUSTNUM, CUS_EMAIL) AS MLPSubExpireDate,
dbo.GetLDCExpiration(CUS_CUSTNUM, CUS_EMAIL) AS LDCSubExpireDate, dbo.IsProspect(CUS_CUSTNUM, CUS_EMAIL) AS AGMProspect,
dbo.IsCurrentCustomer(CUS_CUSTNUM, CUS_EMAIL) AS AGMCurrentCustomer, dbo.IsMLAMember(CUS_CUSTNUM, CUS_EMAIL) AS MLAMember,
dbo.IsMLPMember(CUS_CUSTNUM, CUS_EMAIL) AS MLPMember, dbo.IsLDCMember(CUS_CUSTNUM, CUS_EMAIL) AS LDCMember,
dbo.CalculateTotalRevenue(CUS_CUSTNUM, CUS_EMAIL) AS AGMTotalRevenue, dbo.GetPubCodes(CUS_CUSTNUM, CUS_EMAIL)
AS ProductsPurchased, dbo.GetEmailType(CUS_CUSTNUM, CUS_EMAIL, CUS_RENT_EMAIL) AS EmailType, CUS_COMPANY AS Company,
CUS_CITY AS City
FROM dbo.CUS
WHERE (CUS_EMAIL IS NOT NULL) AND (CUS_EMAIL <> '') AND (CUS_EMAIL_VALID = 'Y') AND (CUS_EMAIL LIKE '%@%.%') AND (CUS_RENT_EMAIL = 'Y' OR
CUS_RENT_EMAIL = 'R' OR
CUS_RENT_EMAIL = 'I') AND (CHARINDEX(' ', CUS_EMAIL) = 0) AND (CUS_EMAIL NOT LIKE '@%')
 Thanks in advance  Michael Reyeros

View Replies !   View Related
Select Statement In Sql Server 2000 Is Taking Too Long??
Hi All,I am facing problem in MS SQL Server 2000. It is behaving slow forselect statements. It is even slower than MS ACCESS. For example, if iuse"Select count(*) from tbl;". i get the results after long time ifthere is more than 100k rows. What might be the possible reasons forthis??ThanksHoque

View Replies !   View Related
SQL 7.0 Restore Taking Long Time Reading Db History
I have a backup mainentance plan that does a full backup daily at 03:00am and then 2 minute transaction log backups throughout the day to a raided hard drive (It is set to overwrite after 2 weeks), When i go into enterprise manager and select the database to restore it just seems to take too long to read the backup history in. Can this time be reduced as i need to be able to restore the database A.S.A.P but still need a point in time restore to within 2 minutes of going down??

Thanks

View Replies !   View Related
SSIS Package Validation Taking A Long Time
I have SSIS Projects taking a long time to open with packages with a large number of data flows.   Is there a way to turn off validation of metadata when a package opens?   Turn off validation during execution on SSIS Service (after previously validated in dev)?   Or be able to control when validation takes place in general?  
 
In my one package (1 of 5) I have 43 data flows (with a single source to target mapping) in 4 sequence containers, and it takes approximately 2-3 seconds per source to target mapping and sequence container to validate which will translate to 1 ˝ to 2 ˝ minutes to open.   When the project with all 100+ tables for the data warehouse goes through validation, I can make coffee in the time it takes to open the project.    I have to delete *.suo file (or verify all packages are closed in the designer and save the project file), and when I open the project, I have to jump immediately to SSISĂ Work Offline to set it to not validate the metadata to be able to work in a timely fashion.   DelayValidation=TRUE does not help much.   
 
Running in debug mode, has an effect of causing packages that were not open and validated to go through validation though I am not running those packages.   Validate once during design and run forever.   
 
Even if I re-open a package that I just closed from designer and had gone through validation, it will go through the validation process again.    
 
It would be great if there could be an on-demand option off the menu bar to allow one to control when validation can take place for a project, or a more granular validation option for a specific data flow or container.   
 
 
 
 
 
 
 
 

View Replies !   View Related
Why Is Taking So Long To Open/create/render The Reports For The First Time?
Hi,
 
Why is taking so long to open/create/render the reports for the first time? Is there any configuration to change this? I don€™t think this behavior is related to Report Execution or cache! I think there is something else going on! Thanks.

View Replies !   View Related
Authentication Taking Long Time In Simple RS 2k Report - Suggestions?!
Hi,

I have an SSRS 2000 report that contains 2 datasets.  Each dataset holds the results of a basic MDX query (the queries come back almost instantaneously in the sample MDX app).  The report also contains a dataset holding results from a very simple stored procedure call which returns only 1 row of data.

Our problem is that the report is taking about 45 seconds to render, which seems ridiculous for the small amount and simplicity of data beign returned.

Looking at the log files it seems that it is taking 15 seconds to be authenticated per olap dataset.

Our connection string is:

Provider=MSOLAP.2;Initial Catalog=Mycatalog;Data Source=MyServer
 
The data source is a shared data source.  Credentials are stored securely in Report Server and we have the 'Use as windows Credentials when connecting to the data source' option checked.

We have tried carrying out an oledb connection test in C# to see if opening the connection takes the same time and it does e.g.

OleDbConnection ole = new OleDbConnection("Provider=MSOLAP.2;Initial Catalog=Mycatalog;Data Source=MyServer");
ole.Open();

We do not think it is a hardware issue as the server has 16GB of Ram and 8 processors.  The version of RS is Enterprise.  RS is on a different server to the Olap cubes.  I am unsure of the service packs that have been applied to RS and SQL server.

My question is therefore, is there anything in the connection string or data source setup that we can change to speed up authentication, or any config setting we can change to make authentication faster?

Regards,

MrPeds
 

View Replies !   View Related
SQL Query Taking Forever
I have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT     dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage,                       dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM         dbo.tblErrorLevel INNER JOIN                      dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN                      dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.

View Replies !   View Related
Query Taking Time
Hi,
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.

View Replies !   View Related
Time Taking Query
Dear 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

View Replies !   View Related
Cte Query Taking Forever
I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..

; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update



thanks

View Replies !   View Related
What Is Wrong With This Query..It's Taking Forever...
 

USE GLPDEMO
GO

select t.name as TriggerName, ta.name as TableName, o.parent_obj
into GLPDemo.dbo.Temp_TablesAndTriggers
from sysobjects o inner join sys.triggers t
on t.object_id = o.id inner join syscomments c
on c.id = t.object_id inner join sys.tables ta
on ta.object_id = o.parent_obj
where xtype = 'tr' and c.text like '%Audit%'


DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)

DECLARE TCursor CURSOR for

SELECT TriggerName, TableName from Temp_TablesAndTriggers

OPEN TCursor

FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable

WHILE @@FETCH_STATUS = 0


select @exestr =  ' DISABLE TRIGGER  GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTable


EXECUTE  sp_executesql @exestr;

FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable

CLOSE TCursor
DEALLOCATE  TCursor

--DROP TABLE #Temp_TablesAndTriggers

View Replies !   View Related
Update Query Taking 23 Seconds
I am not sure if I am in the correct location or if I should be in the SQL forum but here is my question:

I have an update statement that goes out through SQL 2000 through a local linked server to another SQL 2000 server on my machine. When I run the update in Query Analyzer it takes less than a second. When I run it in my C# code using the SqlCommand object and parameters it takes me ~23 seconds. If I remove one of the parameters it goes down to ~15 milliseconds. Has anyone heard of this happening?

The parameter that I remove is a simple char(10) column that isn't the primary key and is used in the WHERE statement. There isn't an index on the field.

23 Seconds
Update table Set column = @val WHERE field = @field AND other = @other
15 milliseconds
Update table Set column = @val WHERE field = 'values' AND other = @other

 

View Replies !   View Related
Stored Procedure Taking More Time Than Sql Query
Hi

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.

View Replies !   View Related
Query Taking Ages For No Apparent Reason
Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-3 minutes) and then fails, I'm assuming because it times out. I then check the SQL by excecuting it via query analyzer and it again takes ages but will work eventually (I'm assuming because this bypasses the timeout settings, but changing these isn't on).

This happens randomly, the scripts will be working fine and then fail a few times before magically working again!

Any ideas? Perhaps some database features that commonly cause this problem? The problem only occurs with one database, all our others are fine but we can't spot any differences!

Any help or tips would really be appreciated.

Thanks.

View Replies !   View Related
Query For Taking Each Word In A String, And Putting A Comma After It?
Hey all, i'm making the pages meta keywords on my site dynamic, and i was wondering is there is a string, for example "Dell 17" Monitor Brand New", that would split it into each word for the meta keywords. example "Dell, 17", Monitor, Brand, New" (and possibly to not put a comma on the last word?) 

View Replies !   View Related
Query Call By App Runs Long, Same Query In QA Doesn't.
Hi Guys,

I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.

When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.

I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.

Any idea what might be going on here?

View Replies !   View Related
Long Query
Hello, Everybody

I have a sql query (insert) that I ran on development server. It took me 12 min. I ran that query on production box and it's running already more than 4 hours. Development database (SQL Server 7) is a mirror copy of production one. What might happen if I try to stop query? If I do stop it, would it rollback all transactions? And is there a possibility of crashing a server?

Thank you very much in advance

View Replies !   View Related
Long Query - 100% CPU
SQL 7.0 SP1 - NT 4.00 EE SP6a - Cluster - Multiprocessor(2)-VB 6.0 SP4.
Maybe a very stupid question,but I need to know if it is possible to assign a low priority to a VB exe that query for long time eating all the CPU (100% in task manager).
Thank you.
Franco

View Replies !   View Related
Long Running Query - Only From .NET
I have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table.  When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds.  When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help! 

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved