August 4, 2008 at 11:14 am
SQL executed from SQL Server Business Intelligence Development Studio takes 25675 ms where as the same SQL when executed via the SQL Server Management Studio takes only 1964 ms. The no of reads are 25628 and 24487 respectively.
What could be the reason for such variation?
August 4, 2008 at 1:10 pm
are both BIDS and SSMS on the same machine? if so, i'd look at the connection you're using in BIDS... are you connecting as efficiently as possible (api used, user rights, etc)? can you provide details about the connection? if they're on different machines, i'd check to make sure the same protocols that are enabled on the fast machine are also enabled on the slow machine (and in the same order); there's there's also the physical connection, machine speed, etc...
August 4, 2008 at 2:53 pm
Both BIDS and SSMS are on the same machine connecting to a remote db server.
I explicitly set the connection network protocol to TCP/IP for both.
The duration difference is still there.
August 4, 2008 at 2:55 pm
can you supply the connection string?
August 4, 2008 at 3:22 pm
For BIDS it is Data Source= ;Network Library=dbmssocn;Packet Size=4096
For SSMS it is Network protocol TCP/IP and Packet Size 4096 bytes.
If there is any other way to get the connection string let me know and I will get it.
Thanks for taking the time to resolve my issue.
August 5, 2008 at 6:17 am
here are a couple of options:
1. create a new data source, select OLE DB and use a connection string similar to the following:
Provider=SQLNCLI.1;Password=
2. click ok
3. if you get an error that says you must specify a username, click the edit button and finish filling out the form
alternatively, when you create the new data source, choose SQL Server as the type, then click the edit button and fill out the form.
hopefully that'll fix your problem, or at least get you a step or two closer to the solution.
August 5, 2008 at 9:51 am
When I change the data source to OleDB O get the error
The data extension OLE DB does not support named parameters.
Use unnamed parameters instead.
Now this is a problem. So I have to use the Microsoft SQL Server connection.
August 5, 2008 at 9:55 am
i apologize... i gave an incomplete connection string (i was on the phone getting some bad news). the complete string should be:
Provider=SQLNCLI.1;Password=*password*;Persist Security Info=True;User ID=*username*;Initial Catalog=*databasename*;Data Source=*servername*
August 5, 2008 at 3:17 pm
Cannot use OleDB as the SQL we use have named parameters.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply