.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

performance in inner join with sub-select

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

may i expect to have diff performance (reads, duration, ...) for:

select (cols)

from table1 as t1 inner join (select col1, col2 from table2) as t2

on t2.col1 = t1.col1


select (cols)

from table1 as t1 inner join table2 as t2

on t2.col1 = t1.col1

??? (consider i have indexes in table1 and table2 on col1)

i mean... does it matter if i join full table with full table or sub-selects (which reduce the number of columns)?

View Complete Post

More Related Resource Links

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

JOIN Performance wise which is better a.id=b.id or b.id=a.id

While joining 2 tables A & B... which one is better? from A a join B b on a.id=b.id --or-- on b.id=a.id   Is there any difference?

Performance tuning > two tables with strings > best query to join them


I have two subqueries which build a string (varchar(200)), first subquery creates 200000 records and second 50000 records, how can I write a select statement to get records from subquery first with string existing in second subquery records.

Will sorting both the sub query results be helpful? Kindly advice for best performing select statement.

3.5 Select Performance


I am developing an application that runs on Windows CE 6 and uses Sql Server Compact 3.5.  When I am doing a simple select statement like the one below it takes about 62 ms at minimum to perform the query and return the results.  In this instance there is only 1 record in the table.  The database is on an SDCard but I have tried it using the onboard flash memory with the same results.  The project I am working on requires data access that is much faster than this, is that possible with Sql Server Compact?  There are no other applications/threads running.

string ConnString = @"Data Source = /SDCard/DB.sdf;mode=Exclusive;"; 
SqlCeConnection conn = new SqlCeConnection(ConnString); 
_Command = new SqlCeCommand();
_Command.Connection = conn; 
_Adapter = new SqlCeDataAdapter(_Command); 
 _Command.CommandText = "SELECT Col1, Col2 FROM Table1 WHERE Col1 = '1234'";
DataTable dt = new DataTable(); 
_Adapter.Fill(dt); //This takes about 500ms which I would expect due to JITing
_Adapter.Fill(dt); //This takes 62ms.

WinCE Hardware Specs
400MHz ARM9 processor

Thanks in advance!

SELECT DISTINCT and INNER JOIN causing failure


edit: SQL Server 2008

INSERT CompanySerialNumbers
SELECT DISTINCT h.TagName, h.Value, Entry = '5', MIN(h.DateTime)
FROM LineNumber L
INNER REMOTE JOIN INSQL.RunTime.dbo.StringHistory h ON L.Tagname = h.Tagname
WHERE L.Line = 'L155' AND h.Value != 'NULL' AND NULLIF (h.Value,'') !- 'NULL'
GROUP BY h.TagName, Value

So the goal of this is to, first, compare the input Line against a database (LineNumber) containing all Tagnames and which Line they relate to.  Then look through the StringHistory database for all those Tagnames, and return only the distinct rows in the select statement.  The error(s) I am getting is/are:

OLE DB provider "INSQL" for linked server "INSQL" returned message "History queries must contain at least one valid tagname".

Cannot execute the query "SELECT tbl11007.DateTime Co11015.Tagname etc" against OLE DB provider "INSQL" for linked server "INSQL".


I have a similar query tested and working, but isn't doing a SELECT DISTINCT, just a SELECT.  I cannot, for the life of me, figure out what the problem is.

Performance issue with select query



I have simple select query as below

SELECT DISTINCT BCEGPrsLinesCd, BCEGCmlLinesCd,CountyFedInfoPrcStdNbr

FROM TPSeleBCEGAddress WITH(nolock)




AND LTRIM(RTRIM(PrefixStreetDirCd))='E'






AND LTRIM(RTRIM(BCEGEffectiveYr))='1998'

Table TPSeleBCEGAddress has 64 Lakhs records in it and it takes around 22 Seconds to execute this query. Please let me know a way to improve performance of this select query.

Primary key defined on this table comprises of 16 columns.






IN,EXISTS or INNER JOIN - which one is the best (performance wise)


hi all,

when we compare  IN,EXISTS or INNER JOIN with performance  wise which one is the best? Any Example to prove it?


Tharindu Dhaneenja

Tharindu Dhaneenja (http://dhaneenja.blogspot.com)

Select performance on a non-indexed table


Hi All,

We were previously using SQL Server 2000 as our production database. We recently switched to SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) ) in our production environment.

We have a database with 3 tables which are not indexed. During peak hours there are multiple inserts happening into this table every minute. We use this database specifically for status updates. All our applications update their current status in the database every few minutes. We have a monitoring application, which reads from this database, basically to determine if the applications are running. Its like a heartbeat system. If the application hasn't updated the database for last few minutes then we know there is a problem. 

Since we moved to SQL Server 2005, we are facing a lot of performance issues. Timeout errors popup every now and then and we are experiencing transaction deadlocks. Below is the error message we see in our logs

'Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Failed SQL => StatusMessages'.

We have started experiencing this problem with SQL Server 2005

Which JOIN style is better from performance point of view?



If I do,

SELECT col1, col2....

FROM table1 AS A


(SELECT col3, col4....

FROM table2) B

ON A.col1 = B.col3

Or, I first take the inner query in a temp table first and then use that temp table directly in the JOIN? Consider that both the tables are really really huge (over 100 million records, and they have proper indexes).




In persuit of learning SQL.

AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

ASP.NET Best Practices for High Performance Applications

This article lists the techniques that you can use to maximize the performance of your ASP.NET applications. It provides common issues, design guidelines, and coding tips to build optimal and robust solutions.

Top .NET Performance Problems and how to avoid them

Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code

High-Performance .NET Application Development & Architecture

It has always been a goal of project architects to plan an effective strategy from the ground up in regards to an new application. All relevant factors are taken into consideration with respect to the application, from its design and layout to a functional website infrastructure. Pre-.NET strategies and design guidelines still effective now were developed with Microsoft's DNA (Distributed interNet Application) platform. This model successfully served the purpose of architecting N(any number of)-Tier (levels) applications. In its basic sense, as in most robust, distributed applications, you'll architect 3 main layers or Tiers: presentation, business rules and data access.

How to improve the performance of ASP.NET MVC web applications

It is an in depth analysis of a Digg-like site and how it went from serving (on a test machine) 6 req/sec to 390 req/sec.

The biggest gain, 74 req/sec to 390 req/sec happened when he introduced data caching and another 25 req/sec where gained when he introduced the compilation of LINQ queries.

High Performance ASP.NET - Speeding Up ASP.NET Pages

have been writing a series of blog posts, which I have named High Performance ASP.NET Websites Made Easy! There is no rhyme or reason to the order of these posts and certainly can be read in any order:

Developing High Performance and Scalable ASP.NET Websites
Avoid Chatty Interfaces Between the Tiers in Your ASP.NET Web Application
ASP.NET Page Profiling - Page Tracing - High Performance and Scalable ASP.NET Websites Made Easy
Web Applications: N-Tier vs. N-Layer - Benefits and Trade-Offs
Query Analyzer - Sql Server Database Indexes and Execution Plans

Improving Application Performance in .Net

Application performance has always been a concern for Web Application developers. This article contains guidelines and tips for maximizing application performance in ASP.NET.

Tips to Improve Your ASP.NET Web site performance

ASP.NET has been developed with lots of features which makes developer life easy. Start from ViewState, PostBack, Server Controls , User Controls and etc. All of them can make your web development experience fun and easy, but improper use of them can cause your site performance slowdown. Most of the controls generate overhead to the client and server. Therefore in this article, I I will list out about things you can do and don't to improve your ASP.NET website performance.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend