Executing a View timesout through Open View

Hello All,

I have created a view that will potentially return a huge number of records around 500,000 rows. When i execute this view by clicking Open View from the SQL Management studio i get the following error

SQL Execution error

Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

However the query executes when paste the generated query of the view in the query designer window. It returns the 500,000 rows. I am wondering if there is some option that i will have to configure to return a large size result set in view designer. I also want to know if this is an inherent problem with view designer.

Any help regarding this will be really appreciated.

Satya

Open View creates a fully editable grid of the rows returned by the view so it is not surprising that it is choking on that much data.

First - this is not the view designer, the view designer opens when you select Modify, and that will let you edit the view definition.

If you do want to work with the data in the view through Open View, then after you select Open View, click the stop button down at the bottom of the window, next to the message that says Retrieving Data... It will stop and show whatever data it retrieved before you clicked the button. Now click the Show SQL Pane button on the toolbar (or Query Designer/Pane menu). In the SQL pane, edit the query by adding a WHERE clause to the SELECT statement that is selecting the data from you view. Now click the Execute SQL button (the exclamation mark) and it will run and return only the rows that match. Now you can selectively pull only a portion of the 500,000 rows at a time and work on them, then change the WHERE clause to select other rows. I can't believe you really want all 500,000 rows on screen at the time - and SQL Server doesn't think it's a good idea either :-)

|||

Hi,

Thanks for your reply. I didnt intend to see all the 500,000 rows of data at a time but did want to know the maximum volume that i can see when i click Open View. The stop button is also disabled when i click Open view and i get this error directly and so i cannot see a partial set of the data the way you had specified. Also i wrote <select a top 1 * > query enveloping the query generated by the view so that i could get atleast the first tuple of the query result but even this was aborted by the same error. The error comes immediately after a minute and no option is enabled for me to do anything once i click Open View or execute.

I was trying to see if this was a configuration issue in VIEW or if i was missing something else. The same query does give the intended results when i run in the query designer. Is there a capacity constraint for the Results grid pane in the View Designer as compared to normal query window.

Thanks again for your help and would appreciate if you can clarify the above too

satya

|||Do you by any chance have a TOP and an ORDER BY in the view definition, or is the view just a standard SELECT?|||It just seems that the CommandTimeout property is not updated in Management Studio when using View, and that it is always stuck at 30 sec. I have the same problem, but it seems impossible to fix.|||I have a view that returns over 500,000 rows, it takes about 5 minutes and never times out. I can click the Stop button at any time. I haven't changed anything from default so I'm not sure what's different.sql

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
Try using a stored procedure if you can (dont know if you can do that in JDBC )|||

Hello Arup,

I would agree that the best way to solve long running query executions is to use a stored procedure which does all the processing of the data on the server and only return to the client the necessary amount of processed data via output parameters. You can use the CallableStatement API provided by the JDBC driver for doing this.

That being said, the Microsoft SQL Server 2005 v1.2 JDBC driver includes a critical 'feature' called "Adaptive Response Buffering" which allow developers to retrieve large amounts of data. The lengthy latency you are encountering is not because of the query execution, but because of the time it takes the driver to spool the data on the client machine before control is given back to the application.

You can ready more about "Adaptive Reponse Buffering" at http://blogs.msdn.com/jdbcteam/archive/2007/05/02/what-is-adaptive-response-buffering-and-why-should-i-use-it.aspx. This functionality if available in the V1.2 CTP1 driver which has the version 1.2.2323.101. You can download it at http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en.

We appreciate your feedback using the newly added functionality of this driver.

Kind Regards,

Jaaved

ExecuteNonQuery while dataReader still open

Hi all!

I basically need to get some records from a table and while looping through them i need to insert some records on other table.

I keep getting this error:

There is already an open DataReader associated with this connection which must be closed first.

The piece of code that I have is like this:

 ...
SqlCommand sqlCmd2 = new SqlCommand(sqlString2, dbConn);
sqlCmd2.Transaction = trans;
SqlDataReader dr = sqlCmd2.ExecuteReader(CommandBehavior.CloseConnection);
//loop through dr
while (dr.Read())
{
string sqlStr = "insert into prodQtyPrice (typeQtyId, prodId, typeId) values(28," + dr["prodId"] + "," + dr["typeId"] +")";
SqlCommand sqlCmd3 = new SqlCommand(sqlStr, dbConn);
//sqlCmd3.Transaction = trans;
sqlCmd3.ExecuteNonQuery();
}
 ...

Also I would like to have the insertions in the same transaction as the previous sql commands.

Thanks a million!

LAMwhy don't you just do that in a procedure? It looks like it's a simple select then insert right?

Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId )
VALUES
SELECT 28, prodID, TypeID From WhereverYouGetThisFrom

GO|||First of all, thanks for answering so fast!

The main reason that is not inside a storedProcedure is requirements. The entire app is not using SP and this has to be done the same way.

LAM|||>>The entire app is not using SP

looks like you have bigger prbs to worry about.
anyway, to answer your q, since the connection is already open with the datareader, you need to open another connection to do the insert.

hth|||you all are making me cross.

command.CommandText = "Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId ) VALUES SELECT 28, prodID, TypeID From WhereverYouGetThisFrom";

Execute phase Memory Error => dtsx finished: Canceled

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?

Execute phase Memory Error

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?