SQL Server Performance Forum – Threads Archive

"insert into" and "select" combo

hi all, is it possible to do a "insert into" and "select" combo in a stored procedure? i have a stored proc that inserts data into a table… what the stored proc does is … insert a vote for a polls. now, i want to only insert if a user has not voted on a poll yet. ////////////////////////////////////////////////////////////// CREATE PROCEDURE dbo.Poll_Votes_Add @Total float(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @NumRaters int, @IpAddress char (15), @Poll_ID bigint AS INSERT INTO Poll_Votes ( [Total], [NumRaters], [IpAddress], [Poll_ID] ) VALUES ( @Total, @NumRaters, @IpAddress, @Poll_ID ) GO ////////////////////////////////////////////////////////////// Can I add a SELECT in there checking to see if a user(IpAddress) has voted on that poll(Poll_ID) yet? … using sql that is. Thanx in advance.

Hi,
yes it is possible.
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

hi ghemant, sample code? plz. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> -a8le

Hi,
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET NOCOUNT On
go create PROCEDURE CurrencyAdd
@Ccode VARCHAR(10),
@Cvalue FLOAT,
@status INT OUTPUT,
@UserID VARCHAR(25) = "GUEST" AS
DECLARE @result INT
BEGIN SELECT @result = count(*)
FROM Currency_Master
WHERE CurrencyCode = @Ccode IF (@result > 0)
BEGIN
SELECT @status = 1001 –THERE IS ALREADY ONE RECORD AVIALABLE WITH SAME ID
RETURN @status
END BEGIN INSERT INTO Currency_master (CurrencyCode,RsValue,UserId,SysDate)
VALUES (@Ccode, @CValue,@UserId,GetDate())
SET @status = @@ERROR
IF(@status != 0)
BEGIN
RETURN
END
RETURN END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO and refer stored procedure from BOL for more. Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

Hemant, selecting count to check if the row exists is not very efficient. It is better to use "if exists(…)":
if exists(SELECT *
FROM Currency_Master
WHERE CurrencyCode = @Ccode)
begin
SELECT @status = 1001 –THERE IS ALREADY ONE RECORD AVIALABLE WITH SAME ID
RETURN @status
end
else begin
INSERT INTO Currency_master (CurrencyCode,RsValue,UserId,SysDate)
VALUES (@Ccode, @CValue,@UserId,GetDate())
SET @status = @@ERROR
IF(@status != 0)
BEGIN
RETURN @insertFailedStatus
END
RETURN @okStatus
end

Hi Mirko,
thanx for correcting. Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

hi guys, the following is what i ending up using, it work beauatifully, thnx… \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\CREATE PROCEDURE dbo.Poll_Votes_Add @Total float(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @NumRaters int, @IpAddress char (15), @Poll_ID bigint AS Begin IF EXISTS ( SELECT * FROM Poll_Votes WHERE [Poll_ID] = @Poll_ID AND [IpAddress] LIKE ‘%’[email protected]+’%’ ) begin SELECT @IpAddress = @@ERROR RETURN @IpAddress end ELSE Begin INSERT INTO Poll_Votes ( [Total], [NumRaters], [IpAddress], [Pool_ID]) VALUES ( @Total, @NumRaters, @IpAddress, @Poll_ID) end End select SCOPE_IDENTITY() GO \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ -a8le

Not sure if Select Count(*) is a very good idea, I will suggest to use Select Count(1) [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />].

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by waqar Not sure if Select Count(*) is a very good idea, I will suggest to use Select Count(1) [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]. <hr height="1" noshade id="quote"></blockquote id="quote">It doesn’t matter, the same execution plan is used for both syntaxes for the same functionality.

Hi waqar, count(*) is always better and sometimes faster than count(1). but never a count(1) is faster than count(*) as count(*) always takes advantage of an index (if any).. please refer this..
In the SQL Server community, one thing that I sometimes encounter is the question on whether you should use COUNT(*) or COUNT(columnname), where columnname is a column in the table that you want to count the rows for. Often the advice given to people in forums and mailing lists is that COUNT(columnname) will perform better than COUNT(*). This is not always the correct advice though, in many cases it is even entirely wrong. Although there are situations where you can (or even should) use COUNT(columnname), you definitely shouldn’t always use it. This incorrect piece of advice is probably based on a lack of understanding of how SQL Server handles data internally.
Description of COUNT() The first thing that you need to know is that there is a difference between the alternative ways of using COUNT( ), and what this difference is. The complete syntax for COUNT( ) is this:
COUNT ( { [ ALL | DISTINCT ] expression ] | * } )
The word expression means any expression except for uniqueidentifier, text, ntext or image data, and it may not use aggregated functions or subqueries. Most often though, expression is just a column in the table. ALL is the default, which means that writing COUNT(expression) is equal to writing COUNT(ALL expression).
COUNT(*) returns the total number of rows in the table, while COUNT(expression) returns the number of rows where the result of the expression is not NULL. Naturally, COUNT(DISTINCT expression) means that duplicates are only counted once. This means that COUNT( ) can return different results depending on how you write it. Myths and facts As I said earlier, many people believe COUNT(columnname) is faster than using COUNT(*), because COUNT(*) would have to read all columns of each row (just like executing a SELECT * FROM MYTABLE statement), while COUNT(columnname) only need to read the specified column. This is not true though, for several reasons.
First of all, SQL Server can’t read just the contents of a single column without reading the entire row. SQL Server stores the rows with the data on 8 KB data pages on disk. These pages contain one or more rows (depending on the size of each individual row, which may be up to 8060 bytes, with some exceptions), and these pages are placed in the internal memory (RAM) when SQL Server needs to access them for any reason. To check the value of a single column (or several of course), an entire page has to be read from disk and placed in memory. The pages may of course already be cached in memory, in which case the read will be much faster, but SQL still needs to read an entire page from memory just to check a single column of a row.
Now, to avoid having to read these entire data pages when all you are really interested in is how many rows there are in a table, SQL Server will use an index instead, if one exists. Indexes are stored in the same way as data, on 8 KB index pages. Since an index is probably not as wide as a data row (the index only consists of some or even one of the columns in the row), an index page can usually fit a lot more rows per page than the data pages can. This means that SQL Server doesn’t have to read as many pages to check the number of rows in the index as it does with the data pages, which is of course a good thing.
This does not only apply to COUNT(columnname_with_an_index_defined_on_it), COUNT(*) will of course also use the index to count the rows. In some cases there may not be an index that covers the specified column in a COUNT(columnname) query, but there is an index defined on another column of the table. In this case COUNT(*) would use this other index to count the number of rows, but COUNT(columnname_without_an_index) would have to read the data pages to check the column for NULL values and count the rows.
To try this for yourself, run the following script in SQL Query Analyzer (if it is not already set to show the results in text mode, use Ctrl-T to set it that way):
USE Northwind
GO SET STATISTICS IO ON SELECT COUNT(*) FROM Orders
SELECT COUNT(CustomerId) FROM Orders
SELECT * FROM Orders SET STATISTICS IO OFF
The statement SET STATISTICS IO ON configures SQL Server to output statistics showing the amount of I/O that was required to execute the query, and you can use it to compare the amount of resources used by different queries to decide which one to use. You can find this output directly after the results of the statement executed. The statistics we are interested in here is the number of logical and/or physical page reads. Logical page reads is the amount of pages (data and/or index pages) that was read from memory, and physical page reads is the number of pages read from disk. On my computer the result of COUNT( ) shows 830 rows for both alternatives, which is probably also what you got if you haven’t added or deleted any rows from the Orders table. Now note the number of logical page reads for these statements (run the script a couple of times if you’re getting physical page reads to cache the data in memory). I have 3 logical page reads for the first alternative, and 21 logical page reads for the second one! Also note that the third statement that SELECTs all of the rows from the table also resulted in 21 logical page reads. This shows us that the second statement had to read all of the data pages just to count the number of rows in Orders because there is no index on the CustomerId column, but the first statement is able to use an index (on my computer the index ShippersOrders was used; I checked the execution plan for the query to find that out) to count the rows.
Which one to use?
As I have shown, using COUNT(*) does certainly not mean poor performance. On the contrary, in some cases you may instead get poor performance from using COUNT(expression). Normally you probably won’t encounter the problem in the example above, as you will probably have an index on the column you specified. What is worse though is that you may receive a different result from what you were expecting! Let’s say that you have a legacy application that uses COUNT(columnname) to count the number of rows of a table, where columnname represents a column that does not allow NULL values. Now, sometime later, the definition for the column is changed to allow NULL values. As soon as someone enters a NULL value in the column, your application will no longer show the number of rows in the table but instead the number of rows with non-NULL values in the specified column! That may not be what the designers of the application intended and expected, and could possibly cause major problems.
But…
So, normally there is no reason not to use COUNT(*). But as I mentioned in the beginning of the article there are situations where you want to (or rather should) use COUNT(expression). One obvious example is of course if you are really only interested in the number of rows where the column value is not NULL. A typical example of a situation like that is when you use COUNT( ) together with another aggregated function. Let’s say we have a table with some sort of measure data, with NULL values in some rows. Now we’re looking for an average of these values. Normally, we would use AVG( ) for this, but to see the point we’ll say we’re not allowed to use it. Compare these two statements and see if you spot the problem:
SELECT SUM(column) / COUNT(*) FROM table SELECT SUM(column) / COUNT(column) FROM table
These statements will return different average results, since SUM( ) ignores NULL values (they are not counted as 0). If the sum is 1500, and the number of rows is 150, of which 50 have NULL in the specified column, the result of the first query will be 10 (1500/150) and the result of the second query will be 15 (1500/100). This is actually a problem that I encounter quite often in my work as a database consultant, and most often it exists due to the fact that the person who wrote the SQL statement where not aware of how NULL values are handled differently in different aggregated functions (SUM( ) and COUNT( ) in the example above).
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Where do you have this from? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

ramkumar.mu, if the above opinions are not from you, then instead of posting the information, show us the link. Thats always good [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

… or at least provide the link in addition to the quote from the article.

I knew, I have read that before:http://www.sqlservercentral.com/columnists/chedgate/adviceoncount.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks Frank. Sometimes ago, I specified that somewhere I read count(*) is faster than count(col). I might have read that link [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

I got this information and its link from the discussion form long back. I stored the content in my machine. as i couldnot remember the link and discussion topic, i pasted the information Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

No problem [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Hi, I have performed testing on a table with 22783221 records. Here is the result
SELECT COUNT(*) FROM table1
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 22 ms. (1 row(s) affected)
Table ‘table1’. Scan count 3, logical reads 43322, physical reads 3, read-ahead reads 39501, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:
CPU time = 7187 ms, elapsed time = 8445 ms. SELECT COUNT(1) FROM table1
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 44 ms. (1 row(s) affected)
Table ‘table1’. Scan count 3, logical reads 43322, physical reads 3, read-ahead reads 39501, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:
CPU time = 7234 ms, elapsed time = 8026 ms.

SQL Server 2000 or 2005? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

SQL 2005 Enterprise. ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~

Interesting! Thanks for the feedback. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Many processes are running on mssql server asynchronously and you never know when they are going to start. So for the conclusion one have to repeat tests many times unless performance difference from just one or a few executions is really big.

]]>