Significant Performance Difference If SELECT Command Contains User

Oct 25, 2006

SQL 2000 Connection String:
user id=MyUserName;password=MyPassword;initial catalog=MyDB;server=MyServer;Connect Timeout=30 
This SELECT statement returns its 10 results nearly instantly:
SELECT * FROM MyTable
Ditto from above, but completes in 30-40 seconds:
SELECT * FROM [dbo].[MyTable]

Ditto from above, but completes nearly instantly:

SELECT TOP 1000 * FROM [dbo].[MyTable]
Obviously I have stopped using the [dbo] syntax in my SqlCommand's (SELECT's and EXECUTE's) but still would like to know why this is.
vr, Rich

View 3 Replies


ADVERTISEMENT

Significant Difference Of Using WITH Statement

Apr 30, 2014

is there a significant difference of using the WITH statement rather than a Temp Table besides less typing. l

View 1 Replies View Related

Significant Performance Reduction After Migration From SQL 2000 To SQL 2005

Jan 5, 2006

Hi all,
 
I am wondering if anyone has similar experience.
 
I did the migration by detaching the database from SQL 2000 running on W2K Pro and attaching it to SQL 2005 running on XP Pro.  Some queries with simple aggregate functions such as AVG() have been slowed by at least an order of magnitude.  I understand that SQL server can intelligently adapt to improve the performance.  I am not sure how much it can improve at this point of time.
 
H

 

View 7 Replies View Related

Transact SQL :: Huge Performance Difference For Same Select Between Environments

Jun 22, 2015

I have encountered a problem with a specific set of tables. The same select yields slightly differing execution plans in two different environments (instances). But the slight variation seems to contain a huge differences in stats. I don't know the significance of these stats. The two tables have the exact same indices.

This is the selcet statement:

SELECT 'xx' FROM DUKS.dbo.Profiler
WHERE DNA_Løbenummer IN
(SELECT DNA_Løbenummer FROM DUKS.dbo.Effektregister
WHERE Sagsnummer = '2015-00002')

View 17 Replies View Related

SQL Server 2012 :: Significant Performance Dropping When Use CASE Statement

Oct 14, 2014

1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:

SELECT A.City_Name, B.Country_Code, B.Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';

That statement works absolutely fine, very fast (less than a second) and returns me 2 records

2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:

SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';

Now to get the same two records it takes 03:55 minutes (!)

I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.

It is SQL 2012 SP1 with compatibility level set to 110

View 9 Replies View Related

[Performance Discussion] To Schedule A Time For Mssql Command, Which Way Would Be Faster And Get A Better Performance?

Sep 12, 2004

1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection

above, which way would be faster and get a better performance?

View 2 Replies View Related

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 Replies View Related

IP Or DNS : Difference In Performance ?

Sep 22, 2006

Hello guys,

I would like to know if there is any meaningful difference in speed performance between using the DNS ("sql.server.com") or the IP address of the sql-server in the connection string.
The advantage of using DNS is that if there is any change in IP, I do not have to change the connection strings, but I do not want to loose speed because of the necessity to resolve the DNS.

Thanks for any help!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View 6 Replies View Related

Date File Difference In Dos Command Script ?

Dec 31, 2002

we are creating script file like below on everyday and I need to find difference between previous script and current script. I can use dos(FC ) command to diffentiate between two file. But How I can differentiate for date itenary file which are generating everyday.

e:scriptdes.proc.123000
e:scriptdes.function.123000
e:scriptdes.table.123000
e:scriptdes.proc.122900
e:scriptdes.function.122900
e:scriptdes.table.122900

Anybody has any suugestion for this issue?.
Thanks,
Ravi

View 2 Replies View Related

Is There Any Difference Performance Wise

Dec 4, 2007

Dear All,
is there any difference performance wise using
select * from my_table
and
select mycol1,mycol2....mycoln from my_table


actually i've read from one article the there is big difference....
please clear my doubt...
thanks in advance

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 8 Replies View Related

What's The Performance Difference Between WITH And A Subquery?

Aug 24, 2006

Hello Everyone,

Does anyone know if there is a performance difference between the new WITH clause t-sql and the subquery?

On a basic functionality level, they seem to perform the same function but I was wondering if there are any performance difference between the 2?



Thanks,

Joseph

View 9 Replies View Related

Performance Difference Of Numeric And String

Sep 15, 2014

I have one question what is performance difference between cluster index on numeric field or string field? I know that numeric is faster but why it is faster?

View 1 Replies View Related

Performance Difference Between Query Executed Through ASP.NET And SSMS

Sep 18, 2007

I have also posted this in microsoft.public.sqlserver.programming.

I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.

The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.

However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer.
Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.

I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.

The query is of the form:

WITH RowPost AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row,
ItemId,
Title,
....
FROM
Items_View WITH(NOEXPAND)
WHERE ItemX >= @minX AND ItemX <= @maxX AND ItemY >= @minY AND ItemY <= @maxY
)
SELECT
*,
(SELECT Count(*) FROM RowPost) AS [Count]
FROM RowPost
WHERE Row >= @minRow AND Row < @maxRow

Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).

This is running against SQL Server Developer Edition.

View 5 Replies View Related

Problem: Performance Difference Between MSDE And SQL Express 2005

Feb 4, 2007

Hello, all, I started out thinking my problems were elsewhere but as Ihave worked through this I have isolated my problem, currently, as adifference between MSDE and SQL Express 2005 (I'll just call itExpress for simplicity).I have, to try to simplify things, put the exact same DB on twosystems, one running MSDE and one running Express. Both have 2 Ghzprocessors (one Intel, one AMD), both have a decent amount of RAM(Intel system has 1 GB, AMD system has 512 MB), and plenty of GB offree disk space. MSDE is running on the Intel system, Express isrunning on the AMD system. To keep things fair I use the exact sameDB's and query on both systems. The DB's were created on MSDE so Isp_detach_db'd them from MSDE and then sp_attach_db'd them to Express(this is how MS says to do a "side-by-side" upgrade, so it'sacceptable to do so). After fighting problems in performancedifferences in different situations I have narrowed the problem downto this:Executing a simple select statement with join clause on the databasesyields a difference in execution time that is quite great. Using theExpress Management program I can run the query against either system(MSDE or Express, the two systems are connected via crossover cable toeliminate any network problems/issues). When running the queryagainst the MSDE system (which is over the network) I consistently get<20 ms response times on the query. When running the query againstthe Express installation (which is in shared memory) I consistentlyget 700 ms or longer response times. Both times are for the TotalExecution Time.The query is simply this: select db1.* from db1.owner.tablename as db1inner join db2.owner.tablename as db2 on db1.pkey = db2.someid wheredb1.criteria = 3So, gimme all the columns from one table in one DB (local to theinstallation), matching the records in another DB (also local to theinstallation), where one field in the first db matches a field in thesecond db and where, in the first db, one column value = 3.The first table has a total record count of 630 records of which only12 match the where clause. The second table has a total record countof about 2,700 of which only 12 match up on the 12 out of 630.Even though the data is the same and I've done the detach and attach,and even done the sp_updatestats, the difference in execution time isremarkable, in a bad way.Checking the Execution Plan reveals that both queries have the samesteps, but, on the MSDE system the largest consumer in the process isthe Clustered Index Scan of the 630 record table (DB1 in my queryexample), using 85%. The next big consumer is a Clustered Index Seekagainst the other table (2,700 rows), using 15%.The Execution Plan against the Express system reveals basically theexact opposite: 27% going to the Clustered Index Scan of the 630record DB1, and 72% going to the Clustered Index Seek of the 2,700record DB2.I'm sorry to be stupid but I have this information but I don't knowwhat to do with it. The best that I can tell from this is that thisis the source of my problems. My problems are that on my currentsystems that my clients use the data is returned to them faster thanthey can click the mouse and that the new system (that is, when theychose (or are forced by attrition) to move to Vista and thus Express2005) the screen pop is like 1.5 seconds. This creates poor userexperience. Worse, one process I allow the users to do goes fromtaking 14-30 seconds to over 4 minutes (all on the same machine withthe same OS and version of my program, so it's not a machine or OS ormy app problem).Anyway, I hope someone can shed some light on this now that I've paredit down some.Thanks in advance.--HC

View 9 Replies View Related

Huge Performance Difference When Running UDF In Workstation Vs Server

Dec 13, 2007

Hi,

I created a CLR UDF that returns a large number of rows, when I run it from my VPC (XP, SQL Server Developer Edition and 1GB Memory) it takes approx 2 min and 30 secs to start displaying the rows (Using Management Studio), when I run the same query in our development server (Win 2003, SQL Server Enterprise Edition, 8 GB Memory and 8 Processors) it takes more than 15 min to start displaying the results, does anybody have an idea why is this happening?

Thanks in advance

View 2 Replies View Related

Performance Difference: Query Window V. Stored Procedure

Oct 24, 2007

Executing the stored procedure took 45 seconds. But copying the code to a query window and setting up the variables (instead of parameters), it took 7 seconds.

In the query window, most of the processing cost (86%) is right up front in a "Distinct Sort." But in exec stored procedure, the cost for this step is 11% and the significant costs are in later "Table Scans."

I don't know why SQL Server would choose different execution plans when the code is identical in each.

Any quick insights?

Many thanks.

View 4 Replies View Related

Query Performance Difference Between Sql Server 2005 And 2000

Aug 1, 2007

Hi,

I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.

The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).

I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.

Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.

Cheers,

Chris

View 4 Replies View Related

Difference Between SYSTEM_USER And USER

Jul 23, 2005

Excuse me, what is the difference between user and system_user and whichfunction should be used in audit trail table ?Thanks.

View 3 Replies View Related

SQL Server Admin 2014 :: Difference In Performance Counter Names In Dmv And Alert Creation Screen

Feb 13, 2015

SQL Server 2014 BI edition.

select * from sys.dm_os_performance_counters returns the object names prefixed with "SQLServer:" (e.g. SQLServer:Databases)

It was expected as in other editions also. Issue is that when we try to crate "SQLServer Performance Condition alert", object names in "Object" list comes without the prefix "SQLServer:" (e.g. Databases). Please see the attached snapshots.

View 4 Replies View Related

Performance Issues With SET DATEFORMAT Command

Nov 9, 2007

Hi,

Are there any know performance problems with using the command SET DATEFORMAT dmy in SQL Server 2000 stored procedures? The reason I ask is because a particular stored procedure is taking 750ms to execute, yet when I remove 'SET DATEFORMAT dmy' line the execution times only takes 20ms (as in twenty - no typo). I mean that's a massive difference, why does SET DATEFORMAT command do this, is there a faster alternative?

As for the sProc itself it does a DateTime comparison on a VarChar field that stores a date time value (yes, I know it's not very well normalised/typed), eg.

AND b.value >= GetDate()

...where value is a VarChar field.

Thanks for any help! :)

View 4 Replies View Related

Extremely Poor Performance On OLE DB Command Transform.

May 1, 2006

In one Data Flow Task (running by itself) I simply have a Raw File Source pushing rows to an OLE DB Command. This command executes an UPDATE command (UPDATE table SET field = ?, anotherfield = ? WHERE thisfield = ?) and performs extremely slow. It's possible to have 62K+ rows needing to be updated and it typically takes this task around 25 - 30 minutes to run.

Is there anything I can do to increase performance?
Are there any options other than the OLE DB Command to perform updates?

Thank you.

View 1 Replies View Related

Difference B/w User Defined Function And Strored Procedure

Jan 3, 2008

Pls tell me the main differences b/w user defined function and stored procedure .

View 2 Replies View Related

Identifying Difference Btwn System And User Objects??

Mar 17, 2000

Does anyone know the logic that Enterprise Manager/SQL Server uses to distinguish system objects from user objects. I've looked through all the sysobjects in the database and there doesn't seem to be any unique logic for seperating all objects. User tables are easily distinguished from system tables (U/S) but thats not the case with stored procedures and views. I'm looking for suggestions...
Thanks,
Adam

View 1 Replies View Related

Difference Between Dbo User And Another User

Feb 15, 2006

hi !

what is difference between dbo user and another user?

View 3 Replies View Related

Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?

Dec 4, 2007

I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks

View 7 Replies View Related

Difference Of Two Select Statements

Aug 19, 2004

Hi All,

I want to find difference of two Select statement in SQL Server 2000. As in Oracle we have "MINUS". Do we have the same thing in SQL Server also.

Thanks with Regards.

-Mohit.

View 2 Replies View Related

What's A Significant LockTimeout Measurment In PERFMON?

Jul 23, 2005

Recently, using PERFMON, I've been rather dismayed to find that ourapplication is averaging 3 - 4 lock timeouts per second, andfrequently has extended periods of several minutes where this figurereaches the hundreds.Average LockWaits/sec are less than 0.05, and TableLockEscalations/secare less than 0.5These last two seem very good to me, and as a result I wouldintuitively expect a LockTimeout figure of near-zero.Can anyone suggest why the measured LockTimeout value might be so high?Is the measured value actually considered "high" at all? Doubtlessthis depends on a number of things (transaction rate, number of usersetc), but a rule-of-thumb opinion would be welcomed.

View 4 Replies View Related

Non Significant '0' Removed From Alphanumeric Code

Feb 2, 2007

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

View 9 Replies View Related

Significant Bug In SSIS Import Function

Feb 8, 2007

I have encountered what I consider to be a significant bug when importing Excel spreadsheets using SSIS.

If in the first data row a particular column does not contain a value (i.e. it is null) then SSIS assumes that every row has a null value in that column. This disfunctional beahaviour has cost me hours and hours of wasted time loading and debugging data.



Hopefully someone from the appropriate area within Microsoft will read this post and get this bug fixed.

View 4 Replies View Related

How To Select Rows With Max Value And Date Difference

Feb 4, 2015

I have here a query which delivers me the user data from the last month. The problem what I have is, if employee have more then one rows in this month, they will be also deliverd. But exactly this is not needed. I need only the last record from last month.

SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS FTE,
a.PFGLTAB,

[Code] ....

Result from Query at the moment:

FIRMAPSNRFELDNRPFLFDNRFTEPFGLTABPFGLTBISKSTNRPSPERSNR
1351022112013082820320101000084000895
14702220,912014100120320101000079000057
1166022112011010120320101000077000543
1364022112013100120150114000072000920
136402220,942015011520321231000072000920

As you can see, PSNR=364 has two rows and i need only the row from last month and last date.Maybe we can use Field PFLFDNR as counter. get only one row for every employee?

like this

FIRMAPSNRFELDNRPFLFDNRFTEPFGLTABPFGLTBISKSTNRPSPERSNR
1351022112013082820320101000084000895
14702220,912014100120320101000079000057
1166022112011010120320101000077000543
136402220,942015011520321231000072000920

View 10 Replies View Related

A Significant Part Of Sql Server Process Memory Has Been Paged Out

Jul 26, 2007

On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?

Error:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.

View 9 Replies View Related

Is There A Way To Show A User-friendly Error When User Did Not Select A Required Parameter ?

Apr 9, 2007

Hi all,



I was wondering whether there is a way to show the user an error when the user did not select a specific parameter.



In my case, I have two optional parameters. The user has to select either one to view the report. If the user does not select either one, I would like to show some sort of an error page indicating so. However, all i am getting is a complete blank with no report in sight. I as a developer know this is an error as a result of missing parameters, but i was wondering whether I could produce a page or direct it to a page so that the user doesn't go all horrified at an empty page ?



Thanks

Bernard

View 4 Replies View Related

Performance Difference Between SQL Server 2005 Standard Edition And Enterprise Edition

Dec 15, 2006

Dear All,We have a database which contains many tables which have millions ofrecords. When We attach the database with MS SQL Server 2005 StandardEdition Server and run some queries (having joins, filters etc.) thenthey take very long time to execute while when We execute same querieson Enterprise Edition then they run 10 times faster than on standardedition.Our database does not use any features which are present in EnterpriseEdition and not present in Standard Edition. We want to know what arethe differences between Standard Edition and Enterprise Edition forperformance. Why should we go for Enterprise Edition when StandardEdition has all the features required.We are presently using evaluation versions of SQL Server 2005 Standardand Enterprise Editions.Thanks and regards,Nishant Sainihttp://www.simplyjava.com

View 23 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved