SQL Server Response is too Slow in reports

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SQL Server Response is too Slow in reports

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2005-10-17 : 22:08:23

Hello Experts.

I developed an application in ASP.NET, it is currently using fully fuctional data manuplation process. Huge amount of data being transfered over the internet..

When more than one peoples executes the same reports or view the data, it either says timeout expires or takes ages to display the data?

My firewall timeout set to 20 Min, Session timeout set to 20 Min etc....

Yes.. one more thing..* My database size is 11 GB using sql server 2000***

Is there any thing I am forgetting? please let me know, your help is much appriciated.

Thanks
Rik

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-10-17 : 22:26:41

ok, the fix is to not send so much data across the internet.

-ec

rikleo2001
Posting Yak Master

185 Posts

Posted - 2005-10-18 : 00:30:17

Yes I did realize that! but my client wants all the queries data in the grid and then he can scroll it whenever he wants... I thought about that to divide that in pages, but that is not my client requirement..

SKR

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-10-18 : 01:46:30

quote:
Originally posted by rikleo2001

Yes I did realize that! but my client wants all the queries data in the grid and then he can scroll it whenever he wants... I thought about that to divide that in pages, but that is not my client requirement..

SKR

how much data are you talking about? you could sniff the connection and see exactly how much data you are returning. There maybe nothing you can do about the performance if you are bringing back tons of data.

You also have not given us any information to help you out. My suggestion really is the only response we can give you based upon the data you have provided.

Tell us how large the table is you are querying, provide DDL for table and the indexes, provide DML for the query in question, provide execution plan for the query, provide STATISTICS IO and STATISTICS TIME data, etc.

-ec

rikleo2001
Posting Yak Master

185 Posts

Posted - 2005-10-19 : 21:43:50

Sorry for being late. I thought I will get email if somebody did post under my thread like other forum, might be there are settings, which I am not familiar with....Anyway

Thanks for your help.

how much data are you talking about? you could sniff the connection and see exactly how much data you are returning. There maybe nothing you can do about the performance if you are bringing back tons of data.??? 2,000 Records only..... which are taking one minute to 2 min.

Here is one of the DML....

CREATE VIEW dbo.dlgV_049_RndSts_All_UAC
AS
SELECT TOP 100 PERCENT dbo.cur_Round.Company_Code, dbo.cur_Round.RoundID AS ROUND, dbo.cur_Round.RoundType AS TYPE, CONVERT(CHAR(10),
dbo.cur_Round.DueDate, 103) AS DUE_DATE, dbo.cur_Round.RoundStatus AS STATUS, dbo.cur_Round.RHD AS DEVICE,
dbo.dlgV_037_RndSts_Total.Properties, dbo.dlgV_037_RndSts_Total.Tasks, CAST({ fn IFNULL(dbo.dlgV_033_RndSts_Compl.cProperties, 0)
} * 100 / dbo.dlgV_037_RndSts_Total.Properties AS decimal(9, 2)) AS COMPLETE, CONVERT(CHAR(10), dbo.cur_Round.CloseDate, 103) AS Close_Date,
dbo.dlg_UserZones.UserAccess_Code, dbo.dlgV_037_RndSts_Total.Properties - { fn IFNULL(dbo.dlgV_033_RndSts_Compl.cProperties, 0)
} AS REMAINING, dbo.dlg_ZoneCodes.Area_Code
FROM dbo.dlg_UserZones RIGHT OUTER JOIN
dbo.dlgV_037_RndSts_Total RIGHT OUTER JOIN
dbo.cur_Round INNER JOIN
dbo.dlg_ZoneCodes ON dbo.cur_Round.Company_Code = dbo.dlg_ZoneCodes.Company_Code AND
dbo.cur_Round.Zone_Code = dbo.dlg_ZoneCodes.Zone_Code ON dbo.dlgV_037_RndSts_Total.Company_Code = dbo.cur_Round.Company_Code AND
dbo.dlgV_037_RndSts_Total.RoundID = dbo.cur_Round.RoundID LEFT OUTER JOIN
dbo.dlgV_033_RndSts_Compl ON dbo.cur_Round.Company_Code = dbo.dlgV_033_RndSts_Compl.Company_Code AND
dbo.cur_Round.RoundID = dbo.dlgV_033_RndSts_Compl.RoundID ON dbo.dlg_UserZones.Zone_Index = dbo.dlg_ZoneCodes.Zone_Index
WHERE (dbo.cur_Round.RoundStatus <> 'I') AND (dbo.cur_Round.RoundStatus <> 'O')

***********

Table has got only 2400 Records...

Here is Show Statistics IO and Time

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(2425 row(s) affected)

Table 'cur_FieldResult'. Scan count 8, logical reads 1152, physical reads 0, read-ahead reads 0.
Table 'cur_Task'. Scan count 8, logical reads 13736, physical reads 0, read-ahead reads 0.
Table 'cur_Round'. Scan count 4, logical reads 33, physical reads 0, read-ahead reads 0.
Table 'dlg_ZoneCodes'. Scan count 4, logical reads 31, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 373, logical reads 748, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 377, logical reads 756, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 401, logical reads 804, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 363, logical reads 728, physical reads 0, read-ahead reads 0.
Table 'dlg_UserZones'. Scan count 4, logical reads 38, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

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

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

SQL Server Execution Times:
CPU time = 2530 ms, elapsed time = 6809 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

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

***************
Here are indexes behind that view.

ALTER TABLE [dbo].[cur_Task] WITH NOCHECK ADD
CONSTRAINT [PK_cur_Task] PRIMARY KEY CLUSTERED
(
[TaskID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[cur_Round] WITH NOCHECK ADD
CONSTRAINT [PK_cur_Round] PRIMARY KEY CLUSTERED
(
[Company_Code],
[RoundID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dlg_ZoneCodes] WITH NOCHECK ADD
CONSTRAINT [PK_dlg_ZoneCodes] PRIMARY KEY CLUSTERED
(
[Zone_Index]
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_dlg_UserZones] ON [dbo].[dlg_UserZones]([UserAccess_Code], [Zone_Index]) ON [PRIMARY]
GO

CREATE INDEX [IdResult] ON [dbo].[cur_FieldResult]([RoundID], [ICP]) ON [PRIMARY]
GO

CREATE INDEX [NCTaskId] ON [dbo].[cur_Task]([ICP], [RoundID]) ON [PRIMARY]
GO

CREATE INDEX [id1] ON [dbo].[cur_Round]([RHD]) ON [PRIMARY]
GO

CREATE INDEX [IX_dlg_ZoneCodes] ON [dbo].[dlg_ZoneCodes]([Company_Code], [Zone_Code]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[cur_FieldResult] ADD
CONSTRAINT [FK_cur_FieldResult_cur_Property] FOREIGN KEY
(
[ICP],
[RoundID]
) REFERENCES [dbo].[cur_Property] (
[ICP],
[RoundID]
)
GO

ALTER TABLE [dbo].[cur_Task] ADD
CONSTRAINT [FK_cur_Task_cur_Property] FOREIGN KEY
(
[ICP],
[RoundID]
) REFERENCES [dbo].[cur_Property] (
[ICP],
[RoundID]
)
GO

ALTER TABLE [dbo].[dlg_ZoneCodes] ADD
CONSTRAINT [FK_dlg_ZoneCodes_dlg_Companies] FOREIGN KEY
(
[Company_Code]
) REFERENCES [dbo].[dlg_Companies] (
[Company_Code]
)
GO

ALTER TABLE [dbo].[dlg_UserZones] ADD
CONSTRAINT [FK_dlg_UserZones_dlg_Users] FOREIGN KEY
(
[UserAccess_Code]
) REFERENCES [dbo].[dlg_Users] (
[User_Code]
),
CONSTRAINT [FK_dlg_UserZones_dlg_ZoneCodes] FOREIGN KEY
(
[Zone_Index]
) REFERENCES [dbo].[dlg_ZoneCodes] (
[Zone_Index]
)
GO

********
I hope all those information will help you to identify the slow speed over internet.

Thanks in advance.

SKR

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-20 : 19:50:41

You can "Subscribe to this topic" (links at top and bottom of page) in order to get an email sent to you when somebody posts a response.

So you're retrieving 2,000 rows out of a table that contains 2,400 rows? That's not a lot of data to be moving across the line. How long does it take to select from that View from Query Analyzer? Why are you using the TOP 100 PERCENT feature in your view? What is the client technology that is displaying the report (Reporting Services? Custom ASP.NET page? Other?) Once the data is retrieved from the SQL Server, what is happening to it?

I think your timeout is occurring due to something other than the data retrieval. Can you insert timer readouts in your code to determine how long it took to retrieve the data vs. how long it took to render the final display?

---------------------------
EmeraldCityDomains.com

rikleo2001
Posting Yak Master

185 Posts

Posted - 2005-10-24 : 18:21:31

Thanks for your favourable reply. I did subscribe now.
It is taking 1 Min 04 seconds, and TOP 100 percent is to specify order by clause in the View. But I checked the differance By taking it off, but it the same results and same time.
Now I noticed one more thing, the same query took 1:4 sec to produce results and the after 5 min i tried it executes in 9 sec.. why is that? heavy load on SQL or What? should I need to turn on the trace?

AFTER retriving the data from from View I am binding a ASP.NET grid with that data and displaying at at client end. and For Report I am using Crystal reports for Vs.net.

Thanks for the reply....

SKR

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-10-25 : 03:02:14

quote:
Why are you using the TOP 100 PERCENT feature in your view

Ajanmark is right you don't need this as there isn't an order by clause in your view. When you take the TOP 100 PERCENT off it may still be cached, if it's not cached keeping this feature out is likely to improve response

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-10-25 : 10:36:06

Is this Microsoft SQL Server we're talking about?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

rikleo2001
Posting Yak Master

185 Posts

Posted - 2005-10-25 : 15:04:44

Thanks for reply...As I mentioned that I tried by taking out the TOP 100 Percent, but there was no significant speed differance.
Is there anything Indexes I need to re-build or create properly?? I posted them above>

Thanks again for reply..

SKR

rajanvarghese
Starting Member

2 Posts

Posted - 2005-11-09 : 16:51:21

Solutions

1. If you are using dot net you can set the command object timeout
option to a higher lelvel value. set cmd.timeout = 900 means 15 minutes. default value is 30 second. If you table takes more than
30 seconds to return a reply to the web server the cmd time out reaches thats why the problem I think

2. If you are using datagrid you can use cashing or paging so that at a time 50 or 60 records can be displayed and the user can navigate to the next page.

Rajan Varghese

rikleo2001
Posting Yak Master

185 Posts

Posted - 2005-11-09 : 17:01:18

Thanks for your reply rajan.

I am using dataadapter to fillup with the data... there is no command timeout feature in adapter.

As far as concerned with Paging, As I mentioned I don't want to use the paging style, as the client requires the whole page with complete data, no matter if it take 2- 5 min.

aLL IS WORKING FINE, but due to extra load on SQL, it is continiously timing out...

Any more great ideas?

SKR

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-11-09 : 18:37:48

Why not kick off a thread in ASP.net, process the data, and when the thread is done, notifiy the session that the user is connected to to display this data? The data could be written out to a file on the server by the thread, and then the web page would simply read and display the data.

Take a look at these:
http://www.fawcette.com/vsm/2002_11/magazine/features/chester/
http://msdn.microsoft.com/msdnmag/issues/03/12/DesignPatterns/

Also, take a look at enabling gzip compression on your page that displays the data. I think IIS6 has this built in, but you can write your own using a .net zip component like the one below. One of their samples is the Gzip compression thing.
http://www.xceedsoft.com/products/ZipNet/

HTH!
Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources