|
Thanks Colin - that worked just fine.
Cheers,
Drew.
|
|
|
|
|
Hi, I have implemented the code below used for accepting parameters to search on a table. Rather than use the standard dynamic sp approach, I decided to use this approach. What I want to know, is if this way would prove slower as I am evaluating Nulls using IsNull, compared to doing the same with Dynamic SQL:
use Northwind
CREATE PROCEDURE SearchProducts (
@ProductName NVARCHAR(40) = null,
@UnitPrice MONEY = null,
@UnitsInStock SMALLINT = null
)
AS
SELECT *
FROM Products
WHERE (IsNull(@ProductName, ProductName) = ProductName) AND
(IsNull(@UnitPrice, UnitPrice) = UnitPrice) AND
(IsNull(@UnitsInStock, UnitsInStock) = UnitsInStock)
GO
|
|
|
|
|
A quick bit of examining the execution plan reveals that for this procedure, when called with a product name but not the other fields, the query optimiser chooses to use a clustered index scan - basically just reading the table from start to end. This is typically the least efficient operation in SQL Server's armoury, although it may get used in preference to an index if the index isn't very selective (if a lot of results are found in the index, the cost of the bookmark lookups, which may well be out of order with respect to the table, may outweigh the cost of just reading the table).
When performing a SELECT with only ProductName in the WHERE clause, it performs an index seek on the ProductName index then a bookmark lookup to return the whole row.
I rewrote your query to a pattern I've used before:
CREATE PROCEDURE SearchProducts2 (
@ProductName NVARCHAR(40) = null,
@UnitPrice MONEY = null,
@UnitsInStock SMALLINT = null
)
AS
SELECT *
FROM Products
WHERE
(ProductName = @ProductName OR @ProductName IS NULL) AND
(UnitPrice = @UnitPrice OR @UnitPrice IS NULL) AND
(UnitsInStock = @UnitsInStock OR @UnitsInStock IS NULL)
GO This had the same effect as yours did, which did surprise me a little.
SQL Server will compile a query plan the first time using the arguments supplied at that time, and will normally cache the plan and reuse it next time. If the arguments that are used will be wildly divergent - lots of values identical in the table but with a few exceptions - you might find that the cached plan isn't good for some values but is for others. Using the WITH RECOMPILE option when creating the procedure forces SQL Server not to cache the plan. Alternatively you can specify WITH RECOMPILE with the EXEC statement to force a recompile at that time.
So I think dynamic SQL is preferable. You can still use parameters with a query string - and you should, to simplify your code (no need to escape special characters) and to avoid the possibility of SQL injection. You simply use a variable name (e.g. @ProductName) in the query text and add a parameter to the command object, the same as you would if you were calling a stored procedure.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I have a treeview control. I was able to generate the tree nodes for this treeview control by reading the structure of the tree from a database table. At each treenode, I show a check box (CheckBoxes is true). On the Sql Server Side, I have a table which has the following structure:
TreeStruct (NodeID int, NodeDesc varchar(50), NodeState tinyint)
This table is pre populated with information in the order in which the tree will be constructed.
I want to be able to do the following:
(a) whenever the CheckState property changes (i.e., a user Checks/Unchecks a node, I want to commit the change to the database
(b) Let the user make as many changes as possible to the Checkboxes, which will change the CheckState property of the nodes. When the user is done, he/she can press the save button, and all these changes will be written to the database.
I am not sure how to bind the data I read from the database to the TreeNode(s) so that I can make the above two things happen.
Help appreciated.
|
|
|
|
|
Has anyone had any problems installing the latest download install for SQLExpress?
I've tried several times installing it on my machine; it churns along perfectly fine until towards the end of the install while 'verifying user accounts' then it tells me I don't have admin rights. But I do!!. Full admin rights.
This is the message that it splits at me:
TITLE: Microsoft SQL Server 2005 CTP Setup
----------------------------------------
Please use an admin account when installing on XP SP1 Systems.
Setup cannot proceed with non-admin accounts on XP SP1 systems.
Refer to the product documentation for more information.
Any ideas ?
I Dream of Absolute Zero
|
|
|
|
|
I'm curious: Why don't you have SP2 installed?
Most people are willing to pay more to be amused than to be educated--Robert C. Savage, Life Lessons
Toasty0.com
Ladder League (beta)
|
|
|
|
|
XP SP2 has not been issued as part of the company standard build as yet.
I guess I could go against their back and install it on my machine anyway. 
I Dream of Absolute Zero
|
|
|
|
|
hi at all,
i've create in the past some application with c# that used access database.
Anyway I can see in the web that much people works with mysql sintax and mysql connections inside their applications.
So i've thought to use this technology, but i think it is different than access database.
Does the user need mysql demon installed on his pc to use my application mysql-based?
|
|
|
|
|
Hi All,
I'm developing a knowledgebase system where in user can create his own knowledgebase by adding articles to different categories.The categories are in tree like structure where in each category node can contain articles and sub categories. I need to provide the user with the option of subscribing for these categories and whenever any changes are made to articles or any of the categories under it, the user should be notified via email. Can someone tell me how I can design the database to store these information and how can i determine the email address to be notified whenever any changes are made under the category for which user has subscribed.
Thanks in advance
Regards
Raghu
|
|
|
|
|
UserTable with UserID and Email address
CategoryTable with CategoryID and UserID
When a change to a category occures, you would search the CategoryTable for the CategoryIDs you want, get the associated UserIDs, then search the UserTable with the UserID to get the email addresses.
|
|
|
|
|
Does anyone have a script that will extract all of the CREATE INDEX statements for a particular database.
I have already created one for triggers, procedures & functions but having difficulties in creating a script for the indices.
Thanks in advance.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
We have a stored procedure written in T-SQL that scans several tables and views, then returns the resulting data.
Executing this stored procedure for the first time on a database with lots of data in it (say, 50000 unique rows at least) will take a LONG time to execute; something like 60 seconds or more. Successive calls to the stored procedure take only 2-3 seconds.
We believe this is due to SQL generating an execution path for the stored procedure. Unfortunately, SQL seems to be throwing away the execution path for this stored procedure: after a few hours (or maybe a day) of executing at speeds of 2-3 seconds, it'll suddenly take 60 seconds or more for no apparent reason again.
I'm curious, does this sound like SQL execution path caching problem? Or is there something else lurking in the DB causing this issue?
Tech, life, family, faith: Give me a visit.
I'm currently blogging about: He has a funny face. And he's my son.
Judah Himango
|
|
|
|
|
It is probably the effect of loading the data from the tables into memory and unloading other tables. Once the data is local after the first query is complete running the query again is fast, then as other queries load data from other tables into the cache, this data slowly gets unloaded and the query takes a long time since it has to hit the disk to reload the data.
There could also be a locking issue if the database is under load and other clients are accessing the same table. If it were the execution plan then it would probably run slow until the table statisitics were updated.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Judah Himango wrote:
I'm curious, does this sound like SQL execution path caching problem? Or is there something else lurking in the DB causing this issue?
The query should be precompiled once it is a stored procedure. I have heard of systems where the stored procedures were very very large and there were lots of them causing problems, however, in most systems it is more likely to be due to data caching issues. When the data is retrieved for the first time it is read into memory, if the SQL Server operates on the same data again then it will most likely be in the cache already and it doesn't have to go out to disk to retrieve it.
To check what is going on you can use
SET STATISTICS IO ON It will show you various information about the I/O activity your query required. e.g.
Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0.
Table 'Employees'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
. This shows that the Tables that were used the number of logical reads (from the data cache), and the number of physical (from disk) reads.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I would agree with Andy as the data is being cached, meaning that the tables are stored in memory after the intial query is executed.
When I used to work on large databases, 200 gigs+, I used a query statement similar to the one below to check the physical IO's during the queiries execution.
select physical_io, * from master..sysprocesses
where spid = 'your 60 second query's spid'
In sysbase I remember there being a column named Line_Num but in SQL Server I only see stmt_start and stmt_end. It might be helpful in determining what part of the procedure is causing the performance issues if any.
However, it would appear that colin may has the better alternative in the end with the maximum execution time of around a minute.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Is it possible to insert data at a specific row? Ie. put a row of data right at the top of a table?
Thanks a lot,
Jim
Did I post well? Rate it! Did I post badly? Rate that too!
|
|
|
|
|
Databases are set based systems, therefore there is no concept of the "top of [the] table". However, clustered indexes mean that even although there is no logical concept there is a physical structure in place which will put a row at the top of the table. So, perhaps what you needing to do is to create a clustered index on the column that you want to determine the sequence of the rows. However, you need to be careful as this will be the order that the rows are physically stored. Clustered indexes do not work so well if the column values are not unique as any searches will have to resort to a sequential search if there are duplicate values.
Alternatively, if all you need is a specific order, for example display purposes, then what you need to do is to create a new column to store some sort of virtual row number, you can then assign a number to it indicating the row's position in the table. When you need to have the rows being returned in a certain sequence you can use the ORDER BY clause on the SELECT statement to return the rows in the order you need.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I have two SELECTs and I want to merge their results using an inner join. Then, I want to insert it into a table... I tried
INSERT INTO tableName
SELECT *
FROM
((SELECT
blah blah blah
)
as numberOne LEFT JOIN
(SELECT
blah blah blah
)
as otherTable
on numberOne.index = otherTable.index
))
I get a syntax error though, so what am I doing wrong?
Thanks a lot,
Jim
Did I post well? Rate it! Did I post badly? Rate that too!
|
|
|
|
|
|
Let's see if I can explain myself  I have an Employees table with PK ID_employee. Then I have a Zones table with fields ID_zone, Description, and ID_employee (That's the employee that has been assigned that zone.)
I have another table, Receipts, where I have a list of receipts to collect for the week, with fields ID_receipt, ID_week (PK key into another table), ID_zone, ID_employee.
The idea is that before printing the receipts, I can change the employee assigned to the zone, and have it automatically changed for every receipt. So, after updating my Zones table, I can execute an UPDATE like this:
<code>DECLARE @@ID_employee int;
SELECT @@ID_employee = ID_employee FROM Zones WHERE ID_zone=@ID_zone;
UPDATE Receipts SET ID_employee=@@ID_employee WHERE ID_zone=@ID_zone AND ID_week=@ID_week; (I know I could put both lines into one with something like a UPDATE Receipts SET ID_employee=(SELECT ID_employee FROM Zones... but for readability I left it as two queries.)
My question is how to automatically excute this UPDATE for all the record in Zones that match a certain criteria (specifically, that have not been marked as inactive -- another column.)
[EDIT: I just remembered about WHERE @ID_zone IN (SELECT ...) but in this case that could be added to the WHERE clause of the UPDATE, but how do I get the employee ID for the SET clause??]
Any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
It looks like you are wanting to iterrate over these two queries until all the updates are done. Is that right? If so, then you can use cursors for that. However, as you have already pointed out a "set based" (as in the mathematical concept) way of doing it you should use that way because that is how SQL Server runs most efficiently. A database system is designed to run set based queries very efficiently, let it do what it is good at.
Your efficient code doesn't look unreadable too me - SQL is a different language. German looks unreadable to me. Spanish looks oddly structured to me, but with some effort I can understand it. English appears to me to be the language everyone should speak because it is so easy to understand. However, I would guess your perspective on that is completely different. It is the same with set based languages (like SQL) Vs. Procedural languages (like C#/VB.NET). If I want to communicate effectively to a database I use a set based language. If I want to communicate effectively to an operating system I use a procedural language and I don't try to coax one style on to the other.
I hope this helps.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
It looks like you are wanting to iterrate over these two queries until all the updates are done.
Basically, what I want to do is this (in a C#-like pseudocode):
foreach(Zone zone in Zones)
{
UPDATE Receipts SET ID_employee = zone.Employee
WHERE Receipts.ID_zone = zone.ID;
}In my edition to the other post, I pointed that I could do something like:
UPDATE Receipts SET ID_employee=????? WHERE ID_zone IN (SELECT ID_zone FROM Zones WHERE Active=1) My only problem is how to get the ID_employee from the current Zones record.
You mention cursos, but I've never used them before (I don't even know what they are). I'll look them up in Google in the meantime.
Thanks for your reply,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Luis Alonso Ramos wrote:
You mention cursos, but I've never used them before (I don't even know what they are).
Don't even learn - They are an abominable evil. Actually, they have their uses, but if you prefer the procedural style of programming then it is too easy to use a cursor. Cursors are slow and clunky. I re-wrote some code a little while ago that replaced a cursor with a set based update. With the cursor it took 20 minutes. With the set based update it took 7 seconds. That is incredibly inefficient. For a handful of rows you might not spot the difference (either way is sub-second), but once a system is in productiona and the customer is hurling a many rows more rows at the server then it starts to make a lot of difference.
I think this should work:
UPDATE Receipts
SET ID_employee=zone.Employee
FROM Receipts, Zones
WHERE Receipts.ID_zone = Zones.ID
AND Zones.Active = 1
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Cool!! it worked! 
Thanks!! I guess this is the reason of those "Colin please help me!" posts: you know a lot and always are willing to share! Thank you very much, really!
And talking about cursors, it they are the devil, why were they invented then? I suppose there is a situation where they are indeed the best choice.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I'm glad it worked out.
Luis Alonso Ramos wrote:
And talking about cursors, it they are the devil, why were they invented then?
There are situations when they are the only solution. However, it is too easy to fall into the trap of using cursors when a set based query would be better.
I remember when I first learned about them and I started thinking that it was easier to think about the problem using cursors (since they are just like for loops but in SQL it is easy to think about how to filter rows one at a time in the loop) than proper SQL set based queries. Set based queries are harder - It takes me longer to write a few lines of SQL than a few lines of C#, but it is worth it for the better results. (I'm not saying C# is bad - they are good for different things and I am really looking forward to being able to write stored procedures in C# in SQL Server 2005)
I think I gave the example of reducing a 20 min process to 7 seconds switching from cursors to set based - so you see how much better a little extra thought is worth.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
