data fetching is faster when a table is open

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

SQL Server 2005 Forums

SQL Server Administration (2005)

data fetching is faster when a table is open

Author

Topic

behbehi
Starting Member

5 Posts

Posted - 2014-05-21 : 06:16:20

Hi guys

I have a performance issue about fetching data from sql server. As my database is huge and Views are complicated, fetching data with ADO.NET takes a lot of time. I have reached the conclusion that IF I OPEN A TABLE FROM MANAGMENT STUDIO, THEN FETCHING IS FASTER FROM ADO.NET!!!

I wanna to know why?!! And is a way to implementing a solution that simulating 'OPEN TABLE FROM MANAGMENT STUDIO' from ADO.NET?

thank u

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-21 : 08:27:34

What do you mean by Open a table from Management Studio? Do you mean you select a few (or all) rows or put it in edit mode, or something else?. Could it simply be that whey you "open" the table from SSMS, SQL Caches part or all of it? That would result in subsequent queries being faster.

I think what you need to do is study the execution plan for the query. Look for scan or spool steps and work out how to eliminate them. Consider unwinding the views so that your resulting query works only from the base tables

behbehi
Starting Member

5 Posts

Posted - 2014-05-21 : 09:12:18

Let meLet me tell you in few steps:
1. I request a report from my c# app and it takes 4 sec to generat
2. I go to Managment Studio and open a table, it's not important which table, just a table
3. I request report from step 1 and it just takes 1 sec to generat

is that caching? Is there any way to enable this caching from ADO.NET?

behbehi
Starting Member

5 Posts

Posted - 2014-05-21 : 09:13:37

Opening a table is just Right Click on table name and click on Open Table

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-21 : 14:51:12

There is no "Open Table" context menu item that I can see (SSMS 2012). I have:

New Table
Design
Select top 1000 Rows
Edit top 200 Rows
Script table as >
View Dependencies
Full-text Index >
Policies >
Facets
Start PowerShell
Reports >
Rename
Delete
Refresh
Properties

...but no "Open"

Caching is built-in in SQL Server, but it sounds like something else. In the Database Properties, do you have AutoClose set to "True"?

behbehi
Starting Member

5 Posts

Posted - 2014-05-22 : 01:37:57

Because I use SSMS 2005
You know, It's not important you 'Select top 1000 Rows' or 'Open table'. If you open and see records in a table, Then fetching of all rows are faster from C# app (If fact from ADO.NET)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-22 : 12:46:30

Did you check the AutoClose property on the Database?

behbehi
Starting Member

5 Posts

Posted - 2014-05-24 : 05:05:54

No It was ON
When I changed it to OFF, Is working as I open a table
Thank u gbritton :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources