|
Hello all. I have a stored procedure that updates a table based on it's own contents. The table contains web site hits and I have a C# function that later on resolves the IP addresses in the table to domain names and updates them in the table.
However before that step, the stored procedure in question sees if there are already matching IP addresses from past hits that had their domains resolved and then updates them accordingly to save time on the nslookup part of it.
Problem is that the stored procedure is getting very slow when all that has changed is the number of rows in the table has increased quite a bit, however the daily number of null domains hasn't really changed at all.
I'm wondering if anyone sees anything inherently wacky or wrong with this stored procedure:
ALTER PROCEDURE dbo.zspDomainSelfUpdate<br />
AS<br />
UPDATE dbo.hits SET dbo.hits.domain = hits_1.domain<br />
FROM dbo.hits INNER JOIN<br />
dbo.hits hits_1 ON dbo.hits.ip = hits_1.ip<br />
WHERE (dbo.hits.domain IS NULL) AND (hits_1.domain IS NOT NULL)
(there is an index on the ip field)
|
|
|
|
|
Hi J,
the problem I see is the WHERE-condition, you will (almost certain) get 2 full-table scans in resolving that. I would try this:
UPDATE dbo.hits SET dbo.hits.domain =
(SELECT dbo.hits.domain FROM dbo.hits hits1
WHERE dbo.hits.ip = hits1.ip
AND dbo.domain IS NOT NULL)
WHERE dbo.hits.domain IS NULL
I guess this leads to a somewhat shorter access-plan.
good luck,
dirk
|
|
|
|
|
Thank you! I'll give that a shot, it makes sense.
|
|
|
|
|
I was reading this article:
http://www.w3schools.com/sql/sql_join.asp[^]
And it shows the following for regular joins:
<br />
SELECT Employees.Name, Orders.Product<br />
FROM Employees, Orders<br />
WHERE Employees.Employee_ID=Orders.Employee_ID<br />
and then the following for INNER JOINS:
<br />
SELECT Employees.Name, Orders.Product<br />
FROM Employees<br />
INNER JOIN Orders<br />
ON Employees.Employee_ID=Orders.Employee_ID<br />
They seem to always return the same results. Is there a difference between the two types of queries above? Or are they the same? Is there any performance reasons or any other reasons when I should favor one type of query above the other?
Thanks!
|
|
|
|
|
I've wondered myself and the best info I could find implies that using an explicit JOIN rather than an implied JOIN helps out the optimization of the query for some types of database servers.
Personally, I think it's just a good habit to stick to explicit JOINS.
|
|
|
|
|
They will both produce the same result, and in most cases they will both optimize to the same execution plan.
Try them Query Analyzer (SQL Server) sometime, and choose to show the execution plan.
Without nipples, breasts would be pointless.
|
|
|
|
|
From SQL Books Online:
Specifying Joins in FROM or WHERE Clauses
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release.
For example, these queries both specify a left outer join to SELECT 23 rows that display the title identification number, title name, and the number of books sold:
-- Join in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
-- Join in FROM clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
In this query, a search condition is also specified in the WHERE clause:
-- Join and search condition in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
AND s.stor_id = '7066'
The condition stor_id = '7066' is evaluated along with the join. The join only selects the rows for stor_id 7066 from the sales table, but because it is an outer join null values are supplied as the store information in all the other rows. This query returns 18 rows.
The join condition can be moved to the FROM clause, and the stor_id condition left in the WHERE clause:
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
WHERE s.stor_id = '7066'
This query returns only two rows because the restriction of stor_id = '7066' is applied after the left outer join has been performed. This eliminates all the rows from the outer join that have NULL for their stor_id. To return the same information with the join condition in the FROM clause, specify the stor_id = '7066' condition as part of the ON join_criteria section in the FROM clause and remove the WHERE clause:
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
AND s.stor_id = '7066'
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Does anybody know how to start/stop MSDE from code (c#) in a machine running Windows 98/ME?
In NT, I use System.ServiceProcess.ServiceController.
With ME, I've tried with SQLDMO.SQLServer2Class (which I'm also using to attach a DB, without problems), using the Start method, but it doesn't seem to work.
Thanks!
|
|
|
|
|
I am doing a data conversion from our old DB design to a new one using a stored procedure. In order to avoid changing the name of the source DB in the sproc (we get different db names with identical schemas from our customers--don't ask me why), I would like to pass in the name of the source DB. Can this be done? I've tried to pass in like this:
CREATE PROCEDURE convert_Customer
@DbName [varchar](255)
AS
DECLARE C_OLD_Customer_Data CURSOR
LOCAL FORWARD_ONLY STATIC
FOR
SELECT @DbName.dbo.Customer.CustomerID,
@DbName.dbo.Customer.LastCompanyName,
As I figured it would, it fails miserably. I think it's more and interpolation problem than anything else. Anyone know if and how this can be done?
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Friends, consider the following query:
select id, name, nick from blahtable where salary=2000
As you can see above i selected three fields from a table. Assume that the query returns only one record.
Now i want the values of three fields returned, to be assigned in variables and then i want to print the values of these variables. How can i do so.
|
|
|
|
|
Using which language(s) ?
|
|
|
|
|
|
Well, if you are doing this purely in SQL Server, then you can go into Query Analyser, run the SELECT query, and print the grid from there.
If you want to start assigning return values to variables and formatting printout (and perhaps changing the SELECT criteria), then you have to look at doing this from a client-side tool, such as VB.NET, or Crystal Reports.
Alternatively, you may be able to write a SQL Stored Procedure which takes criteria, and outputs the 3 fields you need (this gets complicated if you want to send the three fields directly to a printer from a stored procedure)
Is this part of a user application you're developing, or is it a once-off ad-hoc type query you're running?
John.
www.silveronion.com[^]
|
|
|
|
|
I believe this may be the solution
--Declare the Necessary Variables
DECLARE @ID AS int,
@Name AS nvarChar(15),
@Nick AS nvarChar(15)
--Incorporate those Variables
--Into the Select Statement
SELECT @ID = id,
@Name = name,
@Nick = nick
FROM blahtable
WHERE salary = 2000
PRINT @ID
PRINT @Name
PRINT @Nick
Buy the Book 'Advanced Transact-SQL for SQL Server 2000' by Itzak Bengan and Tom Moreau, If you plan on working with SQL, it will change your life
...just kidding but its a powerfull book
Gregory J Lynch
Hack
|
|
|
|
|
try this
declare @id int
declare @name varchar(30)
declare @nick varchar(30)
select @id = id, @name = name, @nick = nick
from blahtable where salary=2000
print @id
print @name
print @nick
Si 
|
|
|
|
|
I need to teach some 'users' a few basics of SQL. They are picking up most of the concepts fairly easily. That was until we came to JOINS. Because of the structure of the database, they'll often have to do some LEFT JOINS in their queries.
Does anybody know of a book / website that explains joins in a simple easy to understand way. (Pictures would be nice)
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
|
Hi!
:-OHow can I select from
InvId Company
------------------
1 a
23 b
56 c
12 a
21 c
this data set:
Company Invoices
-----------------------
a 1,12
b 23
c 56,21
Many thanks in advance!
_____________
stefan bornuz
|
|
|
|
|
|
Thanks Jeremy!
I just hoped that MS SQL has a trick for doing this with a 'simple' query, without the need of procedural processing.
Thanks again,
_____________
stefan bornuz
|
|
|
|
|
Typical situation; Project spec calls for MS SQL 2000, come to install at client, client is running MS SQL 7.
There should not be much of a problem, they are not that different (not like 6.5 was to 7.)
However there is a problem; I backed up the SQL 2000 database. When I come to restore it on SQL 7 it gives an error (Error 3624, tableIndex < *****_ARRAYLEN.)
So I need to know: Can I convert a 2000 backup to 7? Is there a switch I need to set in SQL 2000 when I backup?
Any other bright ideas? Doing a script is no good because there is plenty of data in the database that needs to be retained.
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
Shog9:
Paul "The human happy pill" Watson
|
|
|
|
|
FYI.
The answer is: No, Microsoft SQL 7 cannot restore a backup made by Microsoft SQL 2000.
Doh.
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
Shog9:
Paul "The human happy pill" Watson
|
|
|
|
|
Hi
I am using fallowing table, storedprocedure, function and event handlers.
My problem is that I am abled to update one row successfully only.
If there are rows more than one that satisfy the matching criteria (i-e status=1 in this case, let 9 rows) then
I get the fallowing Error Message:-
"Concurrency Violation : the updateCommand affected 0 records"
What should I do to avoid this Error?
My task was to run a storedprocedure and get the description of
updated rows though event handler but I am doing it for one only,
how multiple rows can be updated in my situation?
What am I doing wrong?
Regards
Mairaj
'////////////
Table1 :-
Column Type
---------------------------
id Auto-Number
Description varchar(50)
satus bit
'/////////////////// Storedprocedure:-
CREATE PROCEDURE dbo.[Test_Update]
AS
UPDATE Table1 SET status = 0, Description = "UpDate test"
WHERE status=1;
GO
'////////// function :-
Public Function Excecute_Test_Update()
Dim MyTrans As SqlTransaction
Dim strConnString As String = "data source=(local);initial catalog=" & "Test" & ";uid=sa;password=;"
Dim connDB As New SqlClient.SqlConnection(strConnString)
Dim da As New SqlClient.SqlDataAdapter()
Dim ds As New DataSet()
Dim dtDB As DataTable
Try
connDB.Open()
Dim cmdDB2 As New SqlClient.SqlCommand()
cmdDB2.CommandType = CommandType.Text
cmdDB2.CommandText = "SELECT status, Description, Id from Table1"
cmdDB2.Connection = connDB
da.SelectCommand = cmdDB2
da.Fill(ds, "Table1")
connDB.Close()
dtDB = ds.Tables("Table1")
Dim strExpr As String
strExpr = "status" & " = " & 1
Dim foundRows() As DataRow
' Use the Select method to find all rows matching the filter.
foundRows = dtDB.Select(strExpr)
da.UpdateCommand = New SqlCommand("Test_Update")
da.UpdateCommand.CommandType = CommandType.StoredProcedure
da.UpdateCommand.Connection = connDB
Dim Len As Integer = foundRows.Length - 1
Dim i As Integer
For i = 0 To Len
foundRows(i)("status") = 0
foundRows(i)("Descrition") = "UpDate test"
Next
MsgBox("Updated count:" & dtDB.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent).Length)
connDB.Open()
AddHandler dtDB.RowChanged, New DataRowChangeEventHandler(AddressOf Row_Changed)
AddHandler da.RowUpdated, AddressOf OnRowUpdated
MyTrans = connDB.BeginTransaction()
da.UpdateCommand.Transaction = MyTrans
da.Update(ds, "Table1")
MyTrans.Commit()
Catch exp As Exception
MyTrans.Rollback()
MsgBox("Exception: " & exp.Message)
End Try
RemoveHandler dtDB.RowChanged, AddressOf Row_Changed
RemoveHandler da.RowUpdated, AddressOf OnRowUpdated
MyTrans.Dispose()
connDB.Close()
End Function
'///////////// handlers :-
' handler for rowchanged event
Private Sub Row_Changed(ByVal sender As Object, ByVal e As DataRowChangeEventArgs)
MsgBox("row changed")
End Sub
' handler for RowUpdated event
Protected Sub OnRowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)
MsgBox("Id " & e.Row("Id").ToString() & " Description " & e.Row("Description").ToString())
End Sub 'OnRowUpdated
|
|
|
|
|
Hi all.
QUESTION 1:
I want to know how Microsoft's data mining solution measures up to those provided by other vendors.
QUESTION 2:
OLAP/Analysis Service comes with Microsoft SQL Server. In addition to the regular data drilling and presentation/charting layer support, it supports two analytical components: Microsoft Clustering (Expectation Maximization) and Microsoft Decision Tree (predictive, classification).
QUESTION 3:
I have no experience with Oracle, PL-SQL, digimine, Cognos, SAS... Can anyone tell me how these products compared to OLAP that comes with SQL server? I know some applications are more specialized for a very specific purpose - clickstream for example.
QUESTION 4:
Software like SPSS, minitab. mathematica offers a whole bunch of analytical tools - from simple column average to clustering to regression. Does any of the OLAP solutions comes with these capability?
QUESTION 5:
What are the most important features that one can expect from a BI solution?
QUESTION 6:
general/specific comment, case studies/application, price and licencing cost estimates will be helpful.
thanks a bunch.
norm
|
|
|
|
|
I have a list of names in my SQL database("Books"), datatable("BookName") that I would like to use to create directories on my computer. The following code work excellent using a .txt file of the book names, however the SQL database has the same identical list of names. I'd like to use the SQL instead of the .txt file approach... Help...........
string strSource = (@"C:\bookName.txt");
DirectoryInfo source = new DirectoryInfo(strSource);
string strTarget = (@"C:\books\");
DirectoryInfo target = new DirectoryInfo(strTarget);
StreamReader sr = File.OpenText(strSource);
string read;
while ((read = sr.ReadLine()) != null)
{
target.CreateSubdirectory(read);
}
sr.Close();
keivn@upgrade1.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
