Query Executes Faster In Grid Mode Than In Text Mode!!
Mar 9, 2000
Hello,everyone!!
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000
rows) But when I execute in the text mode, it takes about 0.40 seconds!!
Why is this difference?
Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!)
Why is it so slow on the text mode & relatively faster in the grid mode?
Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)
Rgds,
Adie
View 1 Replies
ADVERTISEMENT
Aug 15, 2007
Hi,
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
Thank you,
Michael Tzoanos
View 4 Replies
View Related
Jan 18, 2007
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
View 1 Replies
View Related
Jul 25, 2014
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
View 1 Replies
View Related
Jul 27, 2015
how to put sql server database in suspect mode intensely and  get it out from suspect mode to normal mode.
  i am using SQL server 2008 R2
View 5 Replies
View Related
Mar 26, 2005
Recently I read such statments 'When SQL Server is run in "lightweight pooling" mode (fiber mode) and the DTC service is started, unexpected behavior may occur.'
Can someone say anything about fibe mode?I am appreciated for it.:)
View 3 Replies
View Related
Jul 16, 2007
Hi,
Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.
Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?
Thanks very much.
View 1 Replies
View Related
Jan 3, 2006
Hi folks,
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Thank you in advance...
SHJ
View 1 Replies
View Related
Feb 24, 2000
I had my database in 6.5 which i upgraded to 7.0 using SQL Server upgrade wizard.Then I created full text catalog. When I say incremental population It gives me warning that You can create full text indexes but can not execute queries against it as the database is still in SQL server 6.5 mode.What is the reason behind this?
View 1 Replies
View Related
Apr 30, 2008
Hi All,
I am not able to open the package in BIDS. When I open the package I am seeing only the XML. Below I had given what I have done.
First I have installed Visual studio 2008 Professional and next I installed SQL Server 2005 with Integration services, database services, workflow components.
I am able to see the BIDS in Start --> All Programes --> Microsoft SQL Server 2005 --> "SQL Server Business Intelligence Development Studio"
Any help would be appreciated.
Thanks in Advance.
View 3 Replies
View Related
Oct 29, 2007
can t-sql be used to find out which recovery mode is on for a given db?
View 1 Replies
View Related
Jul 15, 2007
I'm using VS 2005, and I built a website with a TableAdapter method using the wizard.I hand wrote a line of code: 1 WITH srtAS
2 (
3 SELECT *,
4 ROW_NUMBER() OVER (ORDER BY date DESC) AS 'RowNumber'
5 FROM table1
6 )
7 SELECT *
8 FROM srt
9 WHERE RowNumber BETWEEN @start AND @end; I didn't find any syntax errors, but the Wizard keeps alerting "the over sql construct or statement is not supported." From my research, I found that it's caused by the Wizard that doesnt support some syntax. Afterall, the problem is:1. I have two parameters @start AND @end. normally if there is no "syntax error", then it will show in the TableAdapter: GetData(@Start,@end). But now since there is this "Syntax error", I won't load these two parameters.2. I saw some solutions to this "syntax error" problem, and they suggested to write query manually, don't use wizard. But I don't know how. thanks in advance-noPC-
View 4 Replies
View Related
Feb 13, 2007
Hi all,
I have a couple of reports with predefined queries. When I try to edit a data set query it goes directly to query design mode losing the query I already had. As you can see this is a real problem because I can't edit queries. How can I switch off design mode? any ideas are pretty much appreciated.
Cheers
View 3 Replies
View Related
Aug 8, 2015
We recently upgraded out SQL version from SQL2008R2 to SQL2014. As such, the compatibility mode changed to SQL2104 (120).
We have several queries that used to run fine that now take forever to bring back results. There are no errors (which surprised me). They just take way too long now. PLus they seem to be causing high I/O and CPU.
If I change the compt level back to SQL2008 - these queries run fine.
QUERY with SQL2008 compt level - finished in 2 minutes.
QUERY with SQL2014 compt level - finishes in 3 hours 22 minutes.
same exact query - same server - only thing changed was compatibility level.
WHat do I look for in the queries that could be causing this? (they look fine but obviously I'm missing something here)..
View 9 Replies
View Related
May 4, 2007
I have a stored proc that runs through my databases doing transaction logs. Currently it uses database names but this keeps being changed as a database will be added without me knowing. Next thing the transaction log job is goosed.
I really need something along the lines of select db from sys???? where mode = 'Full'
Any help appreciated.
MPM
View 1 Replies
View Related
May 18, 2012
I have a very frustrating problem. But given that this is a development forum, I'll keep that for my 'Envelope Lickers Rehab' and instead talk about an issue I'm having.
I have a very large query (line-wise) that executes in less than a minute when run from the SQL Management Studio as well as via Excel Services.Â
It is a stored procedure with one parameter. When I try to prime the designer with this query it gives me the following message:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated."
I've considered cracking open the xml source and manually creating the fields, but I'd rather not go there.
View 8 Replies
View Related
May 20, 2015
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;
[Code] .....
View 6 Replies
View Related
Aug 4, 2006
I need to put my SQL Server 2000 database in "hot mode" similar to the way Oracle can be placed in "hot mode" so we can sync a mirror and split it back off. How do I do this from the command line?
View 2 Replies
View Related
Nov 27, 2001
Have you ever heard of "firehose mode"? I am receiving the following message when trying to update certain tables in our SQLServer 2000 database "Transaction cannot start while in firehose mode.' Do you have any insights?
THANKS!
View 1 Replies
View Related
Jul 21, 2000
has anyone everheard of this. i got this error the other evening and could not find any details on it ... thank s
kim
View 1 Replies
View Related
Dec 13, 2000
Hi all,
I want to know how I can do to change my SQL server licence mode (from per-ser to per-seat) without reinstall SQL Server.
Thanks in advance.
View 3 Replies
View Related
May 21, 2003
Hi,
When I open an application, it prompts me for a message that SQL is locked in exclusive mode by other application.
How to solve this?
thanks in advance
christine
View 3 Replies
View Related
Aug 9, 1999
I know that PerSeat is each individual computer that accesses SQL Server
and PerServer is the number of concurrent connections to the server.
Is Client Access Licenses the same as PerSeat?
View 1 Replies
View Related
Jun 28, 2002
At installation I erred in specifying per seat licensing when the server I'm working on is actually a per processor license. Can I change this or must I reinstall SQL 2K?
View 1 Replies
View Related
Jun 28, 2002
At installation I erred in specifying per seat licensing when the server I'm working on is actually a per processor license. Can I change this or must I reinstall SQL 2K?
View 1 Replies
View Related
Nov 7, 2001
Can the licencing mode of SQL Server 2000 be changed after it is installed or do we need to reinstall.
Thanks
View 1 Replies
View Related
Apr 15, 2004
Hi all,
This may seem like a stupid question but i have a server licensed per processor (4 processors) but would like to have it licensed per user. The licensing setup in control panel only shows options for per processor or per devide. How do i get to license per user without reinstalling?
Thanks in advance.
View 4 Replies
View Related
May 16, 2004
Hi,
I connect to SQL-server from my application with 'sa' account.
It works when SQL-server is configured 'Windows Authentication'
and doesn't work for ' Mixed Mode Authentication'.
How it can be?
View 9 Replies
View Related
Oct 4, 2005
Hi,
Were working on a Smart Client application. Our customers will have .Net Windows forms on their computers that will access SQL Server data on our server.
I've read that Windows Authentication is the most secure authentication mode. However, since our customers computers are not on our network it would be impossible to use Windows Authentication. We'll have to use Mixed Authentication. I just wanted to confirm this in case my understanding of the authentication modes is wrong.
Thanks,
Chris
View 9 Replies
View Related
Nov 15, 2007
Like in SQL2000, can we set the SQL2005 dbs to DBO only mode?
View 1 Replies
View Related
Dec 12, 2007
I was not here for the installation of our current SQL server and those who did the install are no longer with the company. Is there a way I can figure out which mode was used? A query?
Any help would be great!
Kelli
View 3 Replies
View Related
Jul 17, 2007
HiI'm in Enterprise Manager and have made a change to a row.When I try to update I get a message that says:Transaction cannot start, Firehose mode"Can anyone explain what this is and what it means ?ThanksDavid Greenberg
View 5 Replies
View Related
