MS SQL Server :: Extremely Slow Table

Need Help, DTS Is Running Extremely Slow!!!

May 13, 2004

Hi guys.

I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).

The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.

The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.

by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status

i have tried everything and cant seem to get this problem fixed

Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You

Gil

View 5 Replies View Related

Extremely Slow TSQL

Apr 23, 2008

Hey Guys

I have a huge speed issue on one or two of my SQL Tables. I have included the basic design below.

Structure
Id
ParentId
Name

Group
Id
ParentId
Name
Weight

Products
Id
Name

StructureProducts
StructureId
ProductId
Imported

StructureGroups
StructureId
GroupId

GroupProducts
GroupId
ProductId

AnswerDates
Id
AssessmentDate

Scores <-- This table is the slow table
AnswerDateId
StructureId
GroupId (nullable)
ProductId (nullable)
Score >= 0 && <= 100

Ok, Structures are the start of everything. Structures, have children. If a group/product is Linked to a parent or child structure then that group/product is visible along the structure tree flow path. Groups, like structure have children. And also like structures, if a group is given a product, then that product is visible through the structure tree flow path.

Example:
Earth [Structure]
- Asia [Structure]
--- China [Structure]
--- Japan [Structure]
----- Computer Stuff [Group]
------- Desktops [Group]
------- Servers [Group]
------- Laptops [Group]
--------- HP [Product]
--------- Dell [Product]
--------- Fujitsu [Product]
- Europe [Structure]
--- Germany [Structure]
----- Berlin [Structure]
--- Italy [Structure]
----- Rome [Structure]
----- Venice [Structure]
- America [Structure]
--- United States of America [Structure]
----- New York [Structure]
------- Computer Stuff [Group]
--------- Desktops [Group]
--------- Servers [Group]
--------- Laptops [Group]
----------- HP [Product]
----------- Dell [Product]
------- Home Stuff [Group]
--------- Kitchen Stuff [Group]
--------- bedroom Stuff [Group]
----- Washington [Structure]
------- Computer Stuff [Group]
--------- Desktops [Group]
--------- Servers [Group]
--------- Laptops [Group]
----------- HP [Product]
----------- Dell [Product]
----------- Acer [Product]
------- Home Stuff [Group]
--------- Kitchen Stuff [Group]
--------- bedroom Stuff [Group]
----- Chicago [Structure]
------- Computer Stuff [Group]
--------- Desktops [Group]
--------- Servers [Group]
--------- Laptops [Group]
------- Home Stuff [Group]
--------- Kitchen Stuff [Group]
--------- bedroom Stuff [Group]
- Africa [Structure]
--- South Africa [Structure]
----- Johannesburg [Structure]
------- Computer Stuff [Group]
--------- Desktops [Group]
--------- Servers [Group]
--------- Laptops [Group]
----------- Acer [Product]
------- Home Stuff [Group]
--------- Kitchen Stuff [Group]
--------- bedroom Stuff [Group]
----- Durban [Structure]
----- Capte Town [Structure]
- Australasia [Structure]

So the initial steps that happen (with regards to Scoring) are as follows.
1. Insert root score (which would be for a structure, a group, an answer date and either a product or no product
2. Score the next group up along the treeview, using the scores for the groups at the same level as the original group (0 score if no score exists).
3. Continue this till GroupTree is at root (parentid == null)
4. Using the next structure up along the treeview, repeat steps 2 & 3.
5. Continue steps 4 until Structuree is at root (parentid == null)

Example
Scoring a product for Johannesburg Acer Laptop would go as follows
1. Initial score for [Acer] product against Group [Laptop] for Johannesburg.
2. Calculate Score for all products (productid = null) against Laptop for Johannesburg
3. Calculate Score for [Acer] product against Group [Computer Stuff] for Johannesburg
4. Calculate Score for all products against Group [computer Stuff] for Johannesburg
5. Calculate score for [Acer] product against all root groups for Johannesburg
5.1. Group [Comptuer Stuff] and [Home Stuff]
6. Calculate score for all products against all root groups for Johannesburg
6.1. Group [Comptuer Stuff] and [Home Stuff]
7. Calculate score for [Acer] Product against Group Laptop for South Africa
8. Calculate Score for all products (productid = null) against Laptop for South Africa
9. Calculate Score for [Acer] product against Group [Computer Stuff] for South Africa
10. Calculate Score for all products against Group [computer Stuff] for South Africa
11. Calculate score for [Acer] product against all root groups for South Africa
11.1. Group [Comptuer Stuff] and [Home Stuff]
12. Calculate score for all products against all root groups for South Africa
12.1. Group [Comptuer Stuff] and [Home Stuff]
13. Calculate score for [Acer] Product against Group Laptop for Africa
14. Calculate Score for all products (productid = null) against Laptop for Africa
15. Calculate Score for [Acer] product against Group [Computer Stuff] for Africa
16. Calculate Score for all products against Group [computer Stuff] for Africa
17. Calculate score for [Acer] product against all root groups for Africa
17.1. Group [Comptuer Stuff] and [Home Stuff]
18. Calculate score for all products against all root groups for Africa
18.1. Group [Comptuer Stuff] and [Home Stuff]
etc. etc. etc...

This basicly coveres the concept behind the basic scoring methodology. Now the methodology splits into 2. The first Methodology 1, say it should do these calculations using the Exact same date as the original scored date. (Ie. if i do a score today, only scores on today will be used in the calculations). The other, Methodology 2, says that it should do the calculations on the latest available date. (Ie. If i do a score today, only scores from today and the latest before today will be used in the calculations).

Now to add another problem to this already complex process, is that each Group and each product within a structure can have either of the 2 scoring methodologies assigned to it. Also, products can only be scored against the structures and groups that they are assigned to. Ie, Acer exists in Laptop Group, in Johannesburg or South Africa or Africa, but doesnt exist in New York.

Ok, so now that i've explained briefly how this scoring works, let me get to the heart of the problem. Basicly its speed (can clearly see why), though the speed issue only comes
up in 1 Place. And that is where it has to look backwards for the latest available score for the required group, structure and product.

For this to happen i wrote a function
ALTER FUNCTION [dbo].[GetLatestAnswerDateId]
( @StructureId INT,
@GroupId INT,
@ProductId INT,
@AnswerDateId INT )
RETURNS INT
AS
BEGIN
DECLARE @Id INT
DECLARE @Date DATETIME

SELECT TOP 1 @Date = [Date]
FROM [dbo].[AnswerDate]
WHERE [Id] = ISNULL(@AnswerDateId, [Id])
ORDER BY [Date] DESC

SELECT TOP 1 @Id = ad.id--gs.[AnswerDateId]
FROM [dbo].[Scoring] gs
INNER JOIN [dbo].[AnswerDate] ad ON ad.Id = gs.AnswerDateId
WHERE [StructureId] = @StructureId
AND ISNULL([GroupId], -1) = ISNULL(@GroupId, -1)
AND ISNULL([ProductId], -1) = ISNULL(@ProductId, -1)
AND [Date] <= @Date
ORDER BY [Date] DESC

RETURN @Id
END

Now on small amounts of data (1000 rows or so) its quick, though that is due to the fact that the data is minimal, but on large amounts of data this function runs for along time. Specificly in the context of the following when there is 6 months of scoring data (100 000+ rows) to peruse.

SELECT [StructureId], [GroupId], [AnswerDateId], [ProductId], [Score]
FROM [Scoring]
WHERE AnswerDateId = GetLatestAnswerDateId([Structure], [GroupId], [ProductId], null)
AND [StructureId] = South Africa
AND [GroupId] = Computer Stuff
AND [ProductId] = Acer

Any idea's on how to make this quick? or quicker?

My Current runtime for calculating the 2500 base scores (totals 100 000+- rows) takes 15 hours. Though this is an initial calculation and is only supposed to be done once.
Also, this calculations are all correct, so my only issue itself is the speed of the entire process.

Thanks In Adance

Jonathan

WARNING: Running on cold coffee!

View 7 Replies View Related

SSRS2005 Is Extremely Slow

Sep 17, 2007

Hello! I have a query that join five tables and returns around 45000 rows that takes no more than a minute to execute, in management studio, on a SQL Server 2005, 2CPU 32 bit(dual core), 4GB and RAID5 disk system. The O/S is Windows 2003 sp2 Standard Edition.

When the same query is executed in SSRS2005, with some drilldown and summary of drilldown levels, it never stops to execute.

Looking at the hardware in the performance monitor reveals nothing strange except that % CPU-time is around 40 percent. Memory resources over 2 GB are available all the time.

Any suggestions is appreciated!

Any problems with SQL Server 2005 source database running on SQL Server 2000 compatibility level?

Thomas Ivarsson

View 1 Replies View Related

SSIS Becomes EXTREMELY Slow After SP2 Upgrade

Feb 22, 2007

On a dual opteron server, viewing the properties of a task inside a data flow was immediate up until this morning.

We just installed SP2, rebooted, and now you right-click on any task and it takes between 30 secs and 5 minutes before the context menu pops-up.

The server is 99% idle, most of the 16 Gigs of memory are idle as well.

View 1 Replies View Related

Working With A Package Is Extremely Slow

Nov 17, 2006

I have a package that reads the contents of 11 Excel files into various tables. Opening this package in the designer, or with DTExecUI is extremely slow. In both cases when I open the package is takes over 10 minutes to do anything. Visual Studio gives the "Visual Studio is Busy" message for 10 minutes and DTExecUI just hangs. DTExecUI actually hangs twice, once when opening the package and a second time when clicking "Execute" (totalling over 20 minutes). It seems like no matter how I try to run the package it will always hang for 10 minutes before running with no status message or anything. Once it runs it completes quickly with no errors.

The various tasks in the package are fairly simple, most being Source > Data Conversion > Destination.

Any suggestions?

View 4 Replies View Related

Extremely Slow Client Connection To MSSQL

Jan 26, 2007

Hallo

i'm experiencing an extremely slow connection from a WXPP Sp2 client to a MSSQL2000 running on a W2k server. The client is running a VB6 application that connect with Windows authentication: every form requesting data opens with a long delay at the first launch; next attempts run normally fast.

In the same LAN there are some others identical clients, all running fine.

Every other network activity from that client is ok.

Where should i start to investigate from?

View 3 Replies View Related

Visual Studio Interface EXTREMELY Slow

Apr 7, 2006

Hi folks,

I'm working within VS2005/Business Intel studio environment. I've got one master package, which loads about 18 sub-packages as tasks.

After openin the master package (and waiting 5-10 minutes for the packages to open and validate), maneuvering within the IDE is nearly impossible, it is so slow. Context menus can take 30 seconds to open. Certian operations, like closing a window, seem to hang the environment.

Does anyone have any feedback about this kind of ide performance problem?

Thanks, Scott

View 10 Replies View Related

SQL Server Management Studio Run Extremely Slow On Server.

Mar 21, 2007

I had installed SQL 2005 in my server (very high spec server).

but i wonder why every time i launch the management studio, it tooks forever to load... about 5 minutes.

When i right click on a table and click on view properties, it can takes up to 5 minutes to display the detail.

Is there any setting / configuration that i configured wrongly?

View 4 Replies View Related

Hiding Columns -White Space At End Of The Table*****Extremely Urgent

Mar 27, 2008

Hello everyone,
In reports ,My customer requirement is to display column based on selected criteria in UI .
The columns which are not selected by him will hide.
for that we kept an expression in Visibility --> Hide

Code Snippet
= NOT Parameters!Parameters.Value.ToString().Contains("Name")

then coming to HTML Report
It is working fine,but white space coming at end of the Table.
can't we supress the white space?
The white space width is exactly the width of the column which is hidden.
My designing in layout is wrong?
Else is that Problem with the SSRS?
Experts Please let me Know!!!!
Give me Solution!!!
Customer is strictly focusing on that requirement.

***Note: white Space is Some what Acceptable.But My Reports are very big like 45 columns around.When he selects 10 out of 45 then you can assume how much space is coming????????****

View 4 Replies View Related

SQL Server 2012 :: Why Indexes On Table Slow Down DML Operation On Table

Mar 7, 2014

Why the Indexes on table slow down the DML operation on table, what is the exact reason?

View 5 Replies View Related

Extremely New

Jan 26, 2007

Hi,

I am very new to SQL, and am basically self-taught(apart from SQL for Dummies and a couple of other books). I am hoping someone can help me with the 'CONVERT' statement.

My system outputs the date format as '12345'. What I have written so far is this;

select Resprj.Re_code, Res.re_desc, resprj.Pr_code, projects.Pr_desc,Res.Re_status1,
Projects.active, Projects.Pr_start, Projects.Pr_end
from res inner join Resprj on (Res.Re_code = resprj.Re_code)
inner join projects on (projects.PR_code = resprj.Pr_code)
and Projects.Pr_desc like '%C9%'
where projects.active =-1
order by Projects.Pr_code, Res.Re_desc

Could someone please help in regards to using the 'CONVERT' statement to change the date from '12345' to dd/mm/yy.

Thanks

Rob

View 6 Replies View Related

Extremely Large Log File

Mar 25, 2008

I have a test db in SQL Server Express 2005 with a 65MB dat file and a 1.1GB log file!! The production db has a slightly larger dat file and only a 6MB log file. I haven't updated by test db from the production db in a couple of months.
I tried Shrink Database and Shrink File in the Management Studio Express, but the log file size hasn't changed.
How did the log file get so large and how do I fix it?
Thanks for any help.

View 3 Replies View Related

Transaction Log Extremely Large

Oct 10, 2000

Hello everyone,
I'm not sure if this is a problem but I've got a database which is about 1700mg in size (at least that's the allocated space on disk) and the log file is over 4600 mb. I've truncated the log file but it still keeps growing. None of our other databases are this large and there are a lot of transactions performed regularly but it looks odd to me that the log is this big when the data is half the size. How can I find out exactly how much space is being taken up by the data and is there anything I can do that will shrink the size of the log file? I am not really a dba so I'm not sure how crucial this is in the grand scheme of things.
Thanks

View 1 Replies View Related

Extremely Large Log Files?

Aug 16, 2006

We have SQL Server running on a Windows 2003 server, only because Backup Exec requires it. AT the location : C:Program FilesMicrosoft SQL ServerMSSQLData
there is this file: SuperVISorNet_log.LDF which is 15 Gb and is accessed daily. I apologize because I don't know what this is!

My question is: can this file be 'pruned' (for want of a better word) because it's taking up a lot of backup space.

View 17 Replies View Related

Extremely Long Lines

Sep 22, 2006

Hi,

When i open a project in ssis show the message:

"Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file."

I click Yes and my project open normally. Someone know why this happen? My project is small, have one package with any imports excel files to Sql Server 2005.

Thanks

André Rentes

View 1 Replies View Related

Possibly Extremely Simple SQL Query

Jan 27, 2004

Right, I'm no SQL programmer. As I type this, I have roughly the third the hair I had at 5 o'clock last night. I even lost sleep over it.

I'm trying to return a list of records from a database holding organisation names. As I've built a table to hold record versions, the key fields (with sample data) from a View I created to display this is as follows:

record_id-----org_id-----live-----version
====== ===== === =====
1-------------1----------0----------1
2-------------2----------0----------1
3-------------1----------1----------2
4-------------2----------0----------2

as you can see the record id will always be unique. record 3 is a newer version of record 1, and 4 of 2. the issue is thus: i only want to return unique organisations. if a version of the organisation record is live on the system (in this case record id 3), i want to return the live version with its unique record id. i'm assuming for this i can perform a simple "SELECT WHERE live = 1" query.

however, some organisations will have no live versions (see org with id 2). i still wish to return a record for this organisation, but in this case the most recent version ie version 2 (and again - its unique record id)

in actual fact, it seems so much clearer when laid out like this. however, i feel it's not going to happen this end, and so any help would be greatly appreciated.

many thanks in advance,

phil

View 2 Replies View Related

[EXTREMELY URGENT] Sp_attach_db Error

Jul 20, 2005

I am attaching a database with 3 data files.When I execute "exe sp_attache_db..." I obtain this error:database 'POINT' cannot be opened because some of the files could not beactivated.I have deleted its LDF file.Usually I detach my db, then I delete transaction log, and reattach 3 datafiles...Now it doesn'work!!!!!!!!!!Someone can help me?Thanks.

View 1 Replies View Related

Extremely Long Lines Of Text

Sep 25, 2006

I have a dtsx package that works fine with one exception. When I open the dtsx package in BI, it gives me the following message:

Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?

When I respond yes, the package opens and I can edit or execute with no problem. Still, I want to understand what could cause this message to occur and, more importantly, how I can get rid of the message. When I try to simply execute the package I still get the same error and it seems this will be a problem for trying to run the package from SQL Server agent.

It seems likely to me that this message refers to the dtsx file (in xml format) itself. Does that make any sense?

View 2 Replies View Related

Exporting To Excel: Extremely Urgent

Mar 24, 2008

I have an Matrix report in which report output is completely numbers. That is fine but the problem is when i am trying to export it to Excel ,the data is exporting wih error:
Converting numbers stored as text.
i dont know why numbers are Exporting like text format.
please let me know whether it is problem with exporting in ssrs tool itself or else i need to change any properties in RDL file.
Note: This Error i am getting when i am using expression IIF(IsNothing(Fields!Parameter.value),"0",Fields!Parameter.value)

View 5 Replies View Related

Slow INSERTs On A Table

Apr 5, 2007

Hello all. I've got a problem with really slow INSERTs on one (and only one) of the tables in a database. For example, using SQL Management Studio, it takes 4 minutes and 48 seconds to insert 25 rows. There are only about 8 columns in the table and only about 1500 records. All the other tables in the database are very fast for inserts.

Another odd thing uniquely associated with INSERTs on this table: prior to inserting the 25 new rows of data, SQL Management Studio tells me that it inserted 463 rows of data which I know did not happen. Here's the INSERT statement:

INSERT INTO FieldOps(StudySiteID
, QA_StructureID
, Notes
, PersonID)
SELECT DISTINCT StudySiteKey
, QA_StructureKey
, SampleComments1
, '25'
FROM ScriptOutput_Nitrate
WHERE (ScriptOutput_Nitrate.StudySiteKey IS NOT NULL)

and SQL Management Studio (eventually) says:
(463 row(s) affected)
(463 row(s) affected)

(25 row(s) affected)

The table has an index on the primary key (INT data type with auto increment). I tried running the following code to fix things but it made no difference:

USE [master]
GO
ALTER DATABASE [FieldData] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

use FieldData
GO
DBCC CHECKTABLE ('FieldOps', REPAIR_REBUILD) With ALL_ERRORMSGS
GO

USE [master]
GO
ALTER DATABASE [FieldData] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

I'm guessing that the problem might be related to the index (??). I don't know... Does anyone here have a suggestion as to what I should do to fix this problem.

View 9 Replies View Related

ETL Sort Is Very Slow On A Big Table

Feb 11, 2008

Hi,

We have developped an ETL. For development we used small test files (10 000 rows) to test if it works correctly.
This runs in less then a minute

In Test we are using a file which contains all rows (7 million). We did twice a test and we first stopped the process after a week and the 2nd time we stopped the process after a weekend.

We are able to trace the problem to the point where it has to sort the tables.

The proces is pretty simple. We use two connectors to directly connect to the tables.
Then we have two blocks to sort the data. And then we have one block to merge the data.

Should we which to let SQL do the sorting ? Since it is in staging is has no index on that column. A select on the tables with an order by takes 3 minutes to return all those rows.

Any idea's ?

Also is there a page with the best practices for ETL ?

Constantijn

View 4 Replies View Related

Extremely Poor Performance On OLE DB Command Transform.

May 1, 2006

In one Data Flow Task (running by itself) I simply have a Raw File Source pushing rows to an OLE DB Command. This command executes an UPDATE command (UPDATE table SET field = ?, anotherfield = ? WHERE thisfield = ?) and performs extremely slow. It's possible to have 62K+ rows needing to be updated and it typically takes this task around 25 - 30 minutes to run.

Is there anything I can do to increase performance?
Are there any options other than the OLE DB Command to perform updates?

Thank you.

View 1 Replies View Related

EXtremely Long Time In Execution Query

Mar 21, 2007

Hi all,

I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?

Thanks,

Ning

View 3 Replies View Related

Warning: Document Contains One Or More Extremely Long Lines ...

Apr 26, 2007

Hi,

i created a large Integration Services Package. When I start now the Project an open the Package, I get a Message

"Document contains one or more long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

What does this Message mean for me? Which mistake did I?

Thanks a lot

View 1 Replies View Related

Slow SELECT On Single Table

Aug 4, 2000

SELECT * on a 4000 row table is taking more than 12 seconds.
Other larger tables are not nearly as slow.
I've DBCC dbreindex'd, and dbcc showcontig shows density at 100%.

How can I figure out why this is happening?
What are some remedies?

Thanks for your help.

View 1 Replies View Related

Very Small Table Incredibly Slow

Jul 23, 2005

Hello.I am administering a SQL Server (Enterprise Edition on Windows 2003)from some month and can't understand what is going on in the latestweek (when the db grow a lot).The DB is around 250G, and has one table with 1 billion rows. It isperforming in a decent way, but can't understand why a particolar tablehas strong performance problem.I have a stored procedure that read table from table A and insert them,after processing in table B, and then move them in other table (similarto a Star Schema) for reporting.Table B is, for how the SP is written, not more than 3000 lines. TableB is very simple, has 3 rows, and no index.What is very strange is that performance of table B is really slow. IfI do a select count (*) from table_b it takes between 30s & 2minutes toreturn it has 0 lines. When the stored procedure insert 1000 lines, ittakes 20/30 seconds and it takes 20/30 seconds to delete them.To me it doesn't look like a lock problem, because it is slow also whenthe only procedure that access that table are stopped. I did an updatestatistics with fullscan on this table with no improvement.The DB is on a Storage Area Network that should perform decently. TheLUN I use is configured to use a piece of 32 disk that are used also byother application. I don't have performance data of the SAN. Themachine is an HP DL580 with 4 CPU (hiperthreading disabled), 8G of RAM,AWE and PAE and 5G reserved for SQL Server.I don't know what to do to solve this situation. Could it be a"corruption problem" that slow this table so much? is it possible thefact the db grow a lot in the last week created problem also to thissmall and simple table?Do you have any idea or hint on how to manage this situation, orpointer to documentation that can help in analizing this situation?For the ones that arrived till here, thank you for your time andpatience reading my bad english...Best Regards,MamoPSI can't rewrite the stored procedure, because it is part of a closedsource product.

View 16 Replies View Related

Large Table, Really Slow Queries

Jul 26, 2007

I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.

The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:

select
count(dt_date) as Searches
from
SearchRecords
where
datediff(day,getdate(),dt_date)=0

As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).

View 6 Replies View Related

Slow Query With Table Containing Image

Sep 25, 2007

Hi,

I have a table defined as such:
PosterArtId int no 4 10 0 no (n/a) (n/a) NULL
Graphic image no 16 yes (n/a) (n/a) NULL
GraphicFilename varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS

I have a Stored Procedure defined in the database that queries this table (joins with some other tables) that takes about 1 1/2 minutes to return results (running directly in query analyzer). The table itself has 8900 records and the resulting stored procedure returns 33 rows.

I have backed up this database and restored it to another database on a different machine also running SQL Server 2000. When I run the same stored procedure on this 2nd database (note the contents of the database and this table are exactly the same), it runs very quickly - in about 2 seconds.

I'm trying to figure out what is causing the query to run so slow on the original database, which is our production database server (note that none of the other queries seem to be running extra slow on this machine, just this particular one).

I've since been reading up on storing images in the database and I don't think the images are stored "text in row" - I ran the command: ��SELECT OBJECTPROPERTY(OBJECT_ID('tblPosterArt'),'TableTextInRowLimit')�? and got a 0 return value.

My ultimate goal is to figure out:
1) why the timing is so different on the two databases even though the have the same data
2) is there something we can do to speed up the results on our production server

For the first goal, I'm heading down the path that something in the database backup/restore did not recreate the btree storage of the image data in the same manner. Would this be correct? If not, is there some kind of analysis that I can do that will tell me some useful information?

I've run the Stored Procedure for both databases in query analyzer with the "show execution plan", "trace", and "statistics" turned on. In the Execution Plan of the production database I see a significant amount of time in three areas: Nested Loops/Left Semi Join, Clustered Index Scan, and Clustered Index Seek. But being as I'm not a dba (nor do we have one on staff), I'm not sure how to interpret this data. I keep wanting to point to some sort of environment issue since the data is the same between the two machines.

I suppose there is nothing to do about the 2nd goal without knowing why the query on the one machine is taking so long. Any thoughts on how to get more information here?

Thanks,
Beth

View 8 Replies View Related

Table Access Has Become Very Slow After DB Restore

Feb 1, 2008

I have a table that has appx 3.2 million rows. see sp_help

Name Owner Type Created_datetime
-------------------------------------------------------------------
TB_SAAI014_BPD dbo user table 2005-08-10 11:33:23.893

Column_name Type Comp Lngth Prec Scale Nullable
------------------------------------------------------------------------
RowID int no 4 10 0 no
SPHInstID int no 4 10 0 no
BPDInstID int no 4 10 0 no
BMUID varchar no 11 no
InfoImblCfw numeric no 9 12 2 no
BMUPrdNonDel numeric no 9 12 2 no
PrdFPN numeric no 9 13 3 no
PrdBMUBalSrvVol numeric no 9 13 3 no
PrdInfoImblVol numeric no 9 13 3 no
PrdExpdMtrVol numeric no 9 13 3 no
BMUMtrVol numeric no 9 13 3 no
PrdBMUNonDelBidVol numeric no 9 13 3 no
PrdBMUNonDelOfrVol numeric no 9 13 3 no
TranLossFctr numeric no 9 15 7 no
TranLossMtpl numeric no 9 15 7 no
TradUnitName varchar no 30 no
TotTrdUnitMtrVol numeric no 9 13 3 no
BMUAppBalSrvVol numeric no 9 13 3 no
DTCreated datetime no 8 yes
DTUpdated datetime no 8 yes

Identity Seed Inc Not Repl
-----------------------------------------
RowID 0 1 0

RowGUIDcol
-----------------------------
No rowguidcol column defined.

Data Located on File Group
==========================
PRIMARY

Index Name Decsription Keys
----------------------------------------------------------------------------
idx_SPH_BPD clustered, unique located on PRIMARY SPHInstID, BPDInstID

This table has 1 clustered index based on its own unique record ID and that of its parent table record

I have an import process that adds appx 980 rows of data to this table and numerous rows to several other tables as part of a transaction and it ran in about 15 seconds.

However we suffered a server failure and it had to be rebuilt (Svr2k3), SQL 2000 re-installed (with default options) and the data base restored.

The same transaction is now taking 8 to 9 minutes.
I tracked it down to this particular table. Just doing a count(*) takes over 5 minutes. Select * where ID = 1 takes over 5 mins. Also, whenever the table is accessed you can hear the server thrashing the disks. Other tables, although smaller do not seem to be suffering from this masive performance drop..
I've tried droping and recreating the index. I have even created a copy of the table, with index, and still get the same issue with speed.
DBCC CHECKTABLE returns the following but takes 6 and a half minutes
DBCC results for 'TB_SAAI014_BPD'.
There are 3168460 rows in 72011 pages for object 'TB_SAAI014_BPD'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

No errors are shown

A DBCC CHECKTABLE on another table with 230 thousand rows, run at the same time only took 10 seconds

Can anyone please point me in the direction of things to check, try or repair.

Any help greatfully recieved.

Jinx1966

View 10 Replies View Related

Table Variable With Update Slow

May 30, 2007

Hi

I have an update query that joins with 2 table variables. this update updates about 50,000 rows in a table.

update table1

from @table2 t2

inner join @table3 t3

on t2.id = t3.id

where table1.id = t2.id

the problem is that sometimes this update takes forever. If I replace the table variable with regular tables, it only takes about 11 seconds.

I am not sure what the problem is. Could this be a problem with the tempdb. What should I be looking for in the tempdb that might cause a problem.

View 3 Replies View Related

Error - Document Contains One Or More Extremely Long Lines Of Text

May 6, 2008

Hi,

I've created a dataset with 27 measures and 20 query parameters. When attempting to load the report containing this dataset I'm shown the message;

'Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file.'

If I do open the file it does indeed respond very slowly or even hangs.

I can manually format the XML code but amending the code in any way (i.e. using the layout designer to move a chart) removes my formatting and re-introduces the problem.

Are these an unreasonable amount of measures / parameters?

Environment;
VS2005 v8.0.507
MSSQL 2005 9.00.1399.06 Build 3790 SP2
Windows Server 2003 SP2

Many thanks.

View 1 Replies View Related

Why Is SQL Server 7 So Slow? I Only Have About 11500 Rows In The Table

Sep 1, 2000

I wonder how SQL Server 7 can be so slow!! I use an external application (that we made) which reads information from SQL Server 7 databases. What I mean is, one of my allications reads information about "users" and there is about 11500 rows in the table with 34 columns in each. My application shows information about one person at a time. And then there's a scrollbox at the bottom where one can scroll to see other users (about 11500 different users). When I'm using the scrollbars to move down and get information about other users, I see that the CPU-usage is 100% all the time. And the "hour-glass" (windows thingy to show that there's a delay) is shown for maybe a second. And at first it takes almost 10 seconds to (I suppose?) read in the information from the table into my application and show information about the first user.

I don't think it should take almost a second or so to just scroll this list of users. How can SQL Server be so slow? I have 196 megabyte ram on this computer that I use for development. All databases together are less than 15 megabytes. Less than 10 megabytes I suppose.

I mean, in Access97 it was much faster but whas gettins slower for certain occurances with much more data int he tables, and the whole idea of converting the system to SQL Server7 was to got fast responsetimes.
Just to clarify, the client-application and the SQL Server resides on the same computer right now so they don't have to go over some kind of network-conenction etc.

What is the big problem here?

regards, Bob Nachbar.

View 1 Replies View Related