tSQL query - Database Discussion Boards

Marc Clifton10-Sep-04 8:13

10-Sep-04 8:13

between:

SELECT * FROM
Blah1 as T1
INNER JOIN
Blah2 as T2
ON T1.ID=T2.ID
INNER JOIN
Blah3 as T3
ON T1.Id=T3.ID ;

vs.

SELECT *
FROM Blah1 as T1 ,Blah2 as T2,Blah3 as T3
WHERE T1.ID=T2.ID AND T1.ID=T3.ID ;

I've never really understood the advantage of the first syntax, and I'm always using the second syntax.

Marc

MyXaml
Advanced Unit Testing

Re: Is there a performance difference?

Grimolfr10-Sep-04 9:15

Grimolfr

10-Sep-04 9:15

The advantage of the first syntax is legibility. It visually and logically separates the evaluations that are specifically used to define the join(s) from the evaluations that are only used to filter the joined results.

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Re: Is there a performance difference?

je_gonzalez10-Sep-04 10:45

je_gonzalez

10-Sep-04 10:45

If you have access to SQL Query Analyzer, type each statement and then Ctrl + L (Display Extimated Execution Plan).

This will display what steps the server will do and when you move the mouse pointer over each step it will tell you the estimated cost in CPU cycles, using the database current stats.

Re: Is there a performance difference?

Steven Campbell10-Sep-04 18:33

Steven Campbell

10-Sep-04 18:33

The first syntax is far superior in my mind, for the following reasons:
1) legibility
2) ease of maintenance - easily add/remove a join/condition
3) flexibility - it is easier to construct complex queries this way, especially those containing outer joins and/or complex joins.

I do think it is possible that the 1st syntax will lead to better optimized queries, because it gives the optimizer explicit information on what is a join and what is part of the WHERE clause. In a simple query it probably makes no difference, but once the query becomes complex, I'm sure it helps the query optimizer to know these things.

Final point on legibility - I usually write my queries something like this

SELECT * 
FROM Blah1 as T1
   INNER JOIN Blah2 as T2 ON T1.ID=T2.ID
   INNER JOIN Blah3 as T3 ON T1.Id=T3.ID

which is much more legible.

my blog

Re: Is there a performance difference?

Mike Ellison12-Sep-04 5:35

Mike Ellison

12-Sep-04 5:35

Hi all. For what it's worth, the WHERE clause syntax for creating joins was part of the ASNI SQL/86 standard. The INNER JOIN / OUTER JOIN syntax became part of the ASNI SQL/92 standard.

In addition to the other comments made, the JOIN syntax also allows for more functionality, such as the FULL OUTER JOIN supported in some RDBMSs.

How do I clear a datasource from a bound control?

Bart-Man10-Sep-04 4:05

Bart-Man

10-Sep-04 4:05

Greetings,

I have a combo box which I have bound to a datasource, and I want to remove the datasource so it doesn't have anything. I have to use it display some seperate text information.

(ie) How I set the datasource.
comboboxA.DataSource=DataSetTest;
comboboxA.DisplayMember="TableField1";

You can clear a datasource if you bind to a binding manager like this, and show 1 field at a time.
(ie) binding the object
comboboxA.DataBindings.Add("Text", DataSetTest, "TableField1");

(ie) To remove the binding you call.
comboboxA.DataBindings.Clear();

Is there an equivalent for removing a binding on a control while using a datasource?

Thanks in advance for any help! Smile | :)

Re: How do I clear a datasource from a bound control?

sreejith ss nair11-Sep-04 23:55

sreejith ss nair

11-Sep-04 23:55

hi,

Try this,

Combo.DataSource=null;

**************************
S r e e j i t h N a i r
**************************

tSQL query

partt10-Sep-04 2:57

partt

10-Sep-04 2:57

I have this code below, but I want the "Available" column to be the @@Rowcount specific to the GroupID. Right now with what I figured below, it subtracts the rowcount from both GroupID 859 and 860.

CREATE PROCEDURE usp_Groups
@StudyNo char(16)
AS
declare @Fill table (GroupID varchar(5), TesterID char(7))
declare @Ct table (GroupID varchar(5), TesterID char(7), Filled varchar(4))
declare @Out table (GroupID varchar(5), TesterID char(7), Filled varchar(4))

insert @Fill
SELECT DISTINCT tGroup.GroupID, tResultQue.TesterID
FROM tGroup INNER JOIN
tResultQue ON tGroup.GroupID = tResultQue.GroupID
where (tResultQue.QueStatusID >= '20') and
(tResultQue.QueStatusID <= '22') and tGroup.StudyNo = @StudyNo

insert @Ct
select distinct F.GroupID, F.TesterID, @@Rowcount as Filled
from @Fill F
Group by F.GroupID, F.TesterID

insert @Out
select distinct F.GroupID, F.TesterID, Ct.Filled
from @Fill F left join @Ct Ct on (F.GroupID = Ct.GroupID)

SELECT distinct tGroup.GroupID, tGroup.ScheduleStamp, tFacility.FacilitiesDescription, tGroup.NotToExceed, (tGroup.NotToExceed- Gonzo.Filled) as Available
FROM tGroup INNER JOIN
tFacility ON tGroup.FacilityID = tFacility.FacilityID left join @Out Gonzo on (tGroup.GroupID = Gonzo.GroupID) left join @Fill J on (tGroup.GroupID = J.GroupID) left join @Ct Cet on (tGroup.GroupID = Cet.GroupID)
where tGroup.StudyNo = @StudyNo
GO
*************************************************
This is what the output looks like:
GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available
-----------------------------------------------------------------
859|2004-02-24|Building A|15|11
860|2004-02-24|Building A|10|6
861|2004-05-04|Building A|10|NULL
***************************************************
This is what it should look like:
GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available
-----------------------------------------------------------------
859|2004-02-24|Building A|15|12
860|2004-02-24|Building A|10|9
861|2004-05-04|Building A|10|NULL

Thank You!

Re: tSQL query

partt10-Sep-04 3:06

partt

10-Sep-04 3:06

Oops, ok, problem solved.
Thank you.

Getting Server List.

Sumit Domyan9-Sep-04 21:17

Sumit Domyan

9-Sep-04 21:17

Hello All
I want to get a list of all available SQL servers. And then the databases under each server.
Any Solution?

Sumit Domyan
Software Developer
ANALEC INFOTECH

Re: Getting Server List.

David Salter9-Sep-04 22:39

David Salter

9-Sep-04 22:39

The followig links should show you how to get a list of all SQL Servers (or all servers of a particuler type) on your network.

http://www.codeproject.com/csharp/servercombobox.asp[^]

http://codeproject.com/database/serverenum.asp[^]

If you want a list of all the databases in a particular SQL Server instance, use the following SQL

select catalog_name from information_schema.schemata

store your internet favourites online - www.my-faves.co.uk

not detecting local MSDE

Steven Dahlin9-Sep-04 17:00

Steven Dahlin

9-Sep-04 17:00

I have been using the code found in the article: "Finding SQL Servers on the Network" to find any local MSDE/SQL Server installation as well as any remote sites. However, I have found that sometimes after MSDE is installed and the OS has been rebooted and the services indicate that it is up and running I have found that the code will not detect the local MSDE installation.

Sometimes, if I wait a while it will then show up. Sometimes it will never show up. Does anyone have any ideas why this might be??

Thanks,
Steve

Re: not detecting local MSDE

Christian Graus9-Sep-04 17:28

Christian Graus

9-Sep-04 17:28

MSDE takes a while to start, maybe that's your problem ?

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

Extremely slow performance of OleDbDataAdapter.Fill method

rotarinn9-Sep-04 7:05

rotarinn

9-Sep-04 7:05

Hi, I am having a big problem with performance of the OleDbDataAdapter.Fill method on other computers than mine. I am using VisualStudio.NET 2003 to develop my application and it works fine on my computer, both in debug mode and when I build a release version and run that. However, if I set up the release version on another computer, it is extremely slow.

This is the code that fills my dataset

public DsDef getDefFiltered(int nIdVersion)
{
	createDefDataAdaptersFiltered(nIdVersion);
	//My strongly typed DataSet is DsDef
	DsDef dsDef = new DsDef();
	daApplication.Fill(dsDef.tblApplication);
	daLineType.Fill(dsDef.tblLineType);
	daVersion.Fill(dsDef.tblVersion );
	//and a few other da..Fill methods

	return dsDef;
}

My dataadapters are created like this. The conn object is an OleDbConnection object:

daApplication = new OleDbDataAdapter("SELECT * FROM tblApplication", conn);

None of the tables that are being fetched with the dataadapters have very many rows, the may vary from 10 to 150, so the amount of data shouldn't be the problem.

On my development machine this method takes 1.4 seconds but on another machine it takes 19.1 seconds. The same difference in performance applies to all other methods that use the OleDbDataAdapter.Fill method.

I have installed the .NET Framework on all machines and tried with both MDAC 2.7 SP1 and MDAC 2.8 installed, but still have this terrible performance.

Do you guys have any idea what might be causing this?? All suggestions are appreciated.

-Rotarinn

Re: Extremely slow performance of OleDbDataAdapter.Fill method

rotarinn9-Sep-04 8:21

rotarinn

9-Sep-04 8:21

I believe I stumbled across the reason for this myself. The folder in which the application was installed to, included a non-english character in its name. By renaming the folder using only english characters, the problem disappeared and the performance matched the performance of my development computer.

Reading From Procedures?

Sumit Domyan9-Sep-04 2:53

Sumit Domyan

9-Sep-04 2:53

hello friends

I want to read from a procedure. e.g. i want to read how much parameters a procedure is accepting? Any help, plz !

Sumit Domyan
Software Developer
ANALEC INFOTECH

Re: Reading From Procedures?

Colin Angus Mackay9-Sep-04 3:20

Colin Angus Mackay

9-Sep-04 3:20

This code should help. Set the variable @StoredProcedureName as appropriate.

select sc.* from sysobjects AS so
INNER JOIN syscolumns AS sc ON sc.id = so.id 
where so.name=@StoredProcedureName
AND so.xtype='P'

Does this help?

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

Re: Reading From Procedures?

Sumit Domyan9-Sep-04 18:55

Sumit Domyan

9-Sep-04 18:55

thanx a lot
it solved my problem.

Sumit Domyan
Software Developer
ANALEC INFOTECH

DTS packages

Sudee8-Sep-04 22:42

Sudee

8-Sep-04 22:42

I am using sql server to copy data from oracle. So I use DTS to copy a table.

What I have done is use DTS Import/Export Wizard. And I have DTS packages which is schedule every day to update table. But the problem is all the data repeats in my new table in sql database instead of updating data.

Please give me some hint what I can do? and where can I go to learn abt it.

Thank you very much. sudeep

======
Yo need a brain to code.

Re: DTS packages

andyharman12-Sep-04 1:50

andyharman

12-Sep-04 1:50

You could add another SQL task to your DTS package which would execute a "TRUNCATE TABLE <tablename>" before your datapump step.

Stored Procedure

Goodway8-Sep-04 4:35

Goodway

8-Sep-04 4:35

Hi everyone
i am new in writing stored procedure and calling them from .net
can anyone tell me about a link to an ebook or articles about stored procedure.
thanks.

Re: Stored Procedure

Colin Angus Mackay8-Sep-04 5:06

Colin Angus Mackay

8-Sep-04 5:06

SQL Server: Books online - CREATE PROCEDURE[^]

.NET Framework Developer's Guide - Using Stored Procedures with a Command[^]

Does this help?

SQL server logs

Imtiaz Murtaza8-Sep-04 2:08

Imtiaz Murtaza

8-Sep-04 2:08

Friends,
I am running SQL server on my machine which is present in a LAN environment. Some one on the LAN, accessed my SQL server and deleted the data.
What i want to ask is that whether SQL server maintains any kind of laog that who tried to connect it and what query the client executed ? If yes, that how can i view this information. Is there any other alternative ? What i wnat to know is that who connected to my SQL server and what query request came from that machine ?

Imtiaz

Sql Query

rkyawal8-Sep-04 0:41

rkyawal

8-Sep-04 0:41

I have problem with this query..
it is taking too much time to fetch the records ..
can anybody tell me general solution on this.
is their any problem in any join ?
i have main problem in the following condition
( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl))
if only one condition is used then it gives result very fast
so
help me ..

SELECT a.laname, a.cfcc, b.cenid, b.polyid, c.tlid, d.fename, d.fetype, d.frlong as d_frlong, d.frlat as d_frlat, d.tolong as d_tolong, d.tolat as d_tolat, p.polylong, p.polylat, p.water ,e.rtsq, e.long1, e.lat1, e.long2, e.lat2, e.long3, e.lat3, e.long4, e.lat4, e.long5, e.lat5, e.long6, e.lat6, e.long7, e.lat7, e.long8, e.lat8, e.long9, e.lat9, e.long10, e.lat10 FROM ((((
( rt7 as a inner join rt8 as b on a.land = b.land )
inner join rti c on ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl))
)
inner join rt11 as d on c.tlid = d.tlid)
inner join rtp as p on p.cenid = b.cenid and p.polyid = b.polyid ))
left join rt2 as e on d.tlid = e.tlid
WHERE a.laname like 'Mill Pond%' and
(( 3958.682 ) * ( acos( sin( (d.frlat) * 0.01745 ) * sin( ( $latc ) * 0.01745 ) + cos( (d.frlat) * 0.01745 ) * cos( ( $latc) * 0.01745 ) * cos( (d.frlong - ($longc) ) * 0.01745 ) ) )) <=$mile
ORDER BY a.laname

Re: Sql Query

Colin Angus Mackay8-Sep-04 5:13

Colin Angus Mackay

8-Sep-04 5:13

Writing a GIS System?

From my experience trig functions are expensive so you should cut down the amount of data that has to be calculated. To do this, recreate your existing query (without the trig functions) as a subquery, replacing the trig functions with a very basic bounding box - this will cut out a vast quantity of data quickly. You are now left with just a subset of the original data, your outer query can now perform the trig operations on a much smaller set of data.

As for the joins, I'm assuming that these id fields are all primary keys and indexed by default, you may want to index the foreign key side of them to see if it speeds up the query also.

However, from experience, I'd say the trig functions are where your bottle neck is.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.