IF NOT EXISTS and hit counter

I'm creating a web page hit counter sproc. I'm trying to prevent duplicate 
hits for the following:

1. If the session id is the same and web page name is the same.
2. If the session id is the same and web page name is "productdisplay.asp" 
and column "prodID" is the same.

My below sproc is preventing duplicate entries for the special 
"productdisplay.asp" page when the "prodID" is the same. But it is allowing 
duplicate "mypage.htm" pages to be logged. The special "productdisplay.asp" 
page is the product display page that will be hit most often but with 
different prodID variables for different products, so I'm only allowing them 
to be logged once per session for each prodID.

I couldn't think of better logic than I have listed below in the WHERE 
clause. Can someone shed light on a better way to structure an "IF NOT 
EXISTS" logical structure for such a task?


Example:

hwebURL                  hwebSessionID             prodID
----------------------------------------------------------------------------
mypage.htm               123456                         -1
mypage.htm               123456                         -1
productdisplay.asp           123456                                      1
productdisplay.asp           123456                                      5


CODE:

CREATE PROCEDURE hit_Add
(

@hwebDate datetime,

@hwebURL varchar(200),

@hwebSessionID varchar(50),

@prodID int

)

AS

BEGIN

IF NOT EXISTS (

SELECT * FROM t_hitsweb

WHERE (prodID = @prodID AND hwebURL = 'productdisplay.asp' AND 
hwebSessionID=@hwebSessionID) AND

(hwebURL = @hwebURL AND hwebSessionID=@hwebSessionID)

)

BEGIN

INSERT INTO t_hitsweb

(hwebDate, hwebURL, hwebSessionID, prodID)

VALUES

(@hwebDate, @hwebURL, @hwebSessionID, @prodID)

END

END


0
Scott
8/17/2010 9:09:11 PM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
782 Views

Similar Articles

[PageSpeed] 20

Scott (sbailey@mileslumber.com) writes:
> My below sproc is preventing duplicate entries for the special 
> "productdisplay.asp" page when the "prodID" is the same. But it is
> allowing duplicate "mypage.htm" pages to be logged. The special
> "productdisplay.asp" page is the product display page that will be hit
> most often but with different prodID variables for different products,
> so I'm only allowing them to be logged once per session for each
> prodID. 
> 
> I couldn't think of better logic than I have listed below in the WHERE 
> clause. Can someone shed light on a better way to structure an "IF NOT 
> EXISTS" logical structure for such a task?

Your logic is simple, but the way you have the parentheses makes me
think you beilieve they mean something. They don't. You have 5 AND terms,
and one of the add terms is

   hwebURL = 'productdisplay.asp' 

So the IF NOT EXISTS only check for this page. This is probably what you 
want:

IF NOT EXISTS (
  SELECT * 
  FROM   t_hitsweb
  WHERE  hwebURL = @hwebURL 
    AND  hwebSessionID=@hwebSessionID
    AND  (prodID = @prodID OR @hwebURL <> 'productdisplay.asp')

Permit me also to point out that it is essential that you have an 
index on (hwebSessionID, hwebURL, prodID).


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/17/2010 9:58:54 PM
You're speaking of indexes on those 3 fields? Will it make that much 
difference?


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DD7F3F4FDA1CYazorman@127.0.0.1...
> Scott (sbailey@mileslumber.com) writes:
>> My below sproc is preventing duplicate entries for the special
>> "productdisplay.asp" page when the "prodID" is the same. But it is
>> allowing duplicate "mypage.htm" pages to be logged. The special
>> "productdisplay.asp" page is the product display page that will be hit
>> most often but with different prodID variables for different products,
>> so I'm only allowing them to be logged once per session for each
>> prodID.
>>
>> I couldn't think of better logic than I have listed below in the WHERE
>> clause. Can someone shed light on a better way to structure an "IF NOT
>> EXISTS" logical structure for such a task?
>
> Your logic is simple, but the way you have the parentheses makes me
> think you beilieve they mean something. They don't. You have 5 AND terms,
> and one of the add terms is
>
>   hwebURL = 'productdisplay.asp'
>
> So the IF NOT EXISTS only check for this page. This is probably what you
> want:
>
> IF NOT EXISTS (
>  SELECT *
>  FROM   t_hitsweb
>  WHERE  hwebURL = @hwebURL
>    AND  hwebSessionID=@hwebSessionID
>    AND  (prodID = @prodID OR @hwebURL <> 'productdisplay.asp')
>
> Permit me also to point out that it is essential that you have an
> index on (hwebSessionID, hwebURL, prodID).
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Scott
8/18/2010 5:42:30 PM
Scott (sbailey@mileslumber.com) writes:
> You're speaking of indexes on those 3 fields? Will it make that much 
> difference?
 
I expect a table that counts web hits fill up quickly. You don't want an
execution time of 1 second, only to register a page hit, do you?



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/18/2010 9:55:01 PM
When I make a primary key, doesn't that at least create the correct type of 
index for that column or do I need
to create another index for that column?


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DD8F34C17A49Yazorman@127.0.0.1...
> Scott (sbailey@mileslumber.com) writes:
>> You're speaking of indexes on those 3 fields? Will it make that much
>> difference?
>
> I expect a table that counts web hits fill up quickly. You don't want an
> execution time of 1 second, only to register a page hit, do you?
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Scott
8/21/2010 11:15:05 PM
Scott (sbailey@mileslumber.com) writes:
> When I make a primary key, doesn't that at least create the correct type
> of index for that column or do I need to create another index for that
> column? 
 
In SQL Server, a primary key is always implemented as a unique index.
However, as I understand your question, I can't see that you have a 
primary key on (hwebURL, hwebSessionID, prodID), since prodID is only 
applicable for a certain value of hwebSessionID. You could have a unique
index, even a UNIQUE constraint on those columns though. The assumption is
then that prodID would be NULL for all other values of hwebURL. And a 
primary key can only consist of non-nullable columns.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/22/2010 8:28:05 AM
Reply:

Similar Artilces:

Add more existing contacts to an account
I would like to know if existing contact's can be added to an account. The thing is that if you open an account, you can add related contact's, but there is not a possibility to add existing contacts to an account, neither there is a possibility to search for existing contacts and add them to an existing account. I am looking for a solution, With regards, Peter Klapwijk Services in IT Open the existing Contact and change Parent Account "Peter Klapwijk" <PeterKlapwijk@discussions.microsoft.com> wrote in message news:3C4996A9-7198-4D76-A2E2-F369C8E35D1E@micros...

if exists
Hello, 1> IF EXISTS (SELECT * from TABLE_A WHERE A = 1) EXEC StoredProcedure 2> IF EXISTS (SELECT B from TABLE_A WHERE A = 1) EXEC StoredProcedure I'm hesitating between 1> and 2>. There are 1000 rows and 23 columns. Do you think 2 is better than 1? or it's same. Thanks for your advise in advance. No difference. It doesn't matter whatever you put in the column list, the optimizer know it is an existence check for rows and doesn't care to materialize any column anyhow. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sq...

Checking for the existence of a dynamically added style
I have a custom control that I would like to have add style rules to the Page that contains it. I know how to do this, using the following method: Me.Page.Header.StyleSheet.CreateStyleRule However, I only want to add the style rule once, since it is the same for every instance of the custom control. When adding scripts, I would simply use the following two statements to add a script and check whether it was already added: Me.Page.ClientScript.RegisterClientScriptBlock Me.Page.ClientScript.IsClientScriptBlockRegistered But I could not find anything like this for stylesheets...

This email already exist in this organisation. ID: c10312e7
Hello, We are using SBS 2003 in a small organisation (1 server, 4 workstations) I am trying to add an email address (xxx@yy.nl) to a public folder. This email address previously was assigned to a (the only) distribution group, but was removed there. The public folder xxx gets an email addres of xxx1@yy.nl and if I change it to xxx@yy.nl the error message appears. I can't assign this address back to the group either. If I send an email to this address the notification states that it's refused by the public folder store. Searched all the discussions concerning c10312e7. I have tried th...

Website Counter
I have a Website for the Local Snooker League with over 70 pages , which I publish using Publisher 2000. The webspace provider shows details of how many people have visited the site. Is their a way , I can find out what pages they are visiting so that I can find out what areas they are mainly interested in. Any help or suggestions would be appreciated. -- sarnia Ask this here: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.publisher.webdesign&cat=en_US_1845e149-582a-4e62-8d76-f1c90f948f44&lang=en&cr=US -- Mary Sauer http://msauer.mvp...

Check if a number exists in a range?
Is there any way (hopefully easy, no code) to check if a number exist in a range? For example, A1 to A5 contains the numbers 1, 2, 3, 4, 5. I want t check if range A1:A5 contains, for example 2. Just have a true o false return. Thanks -- gkast ----------------------------------------------------------------------- gkaste's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2452 View this thread: http://www.excelforum.com/showthread.php?threadid=38684 One way: =COUNTIF(A1:A5,2) > 0 In article <gkaste.1s4b72_1121274441.7749@excelforum-nospam.com>,...

Locate existing record-update it on the form
I placed a command button on a form and want to use it to search for an existing record and then populate the form with that record so I can update/add to it. The form's record source is a table (tblRFPTracking). I'm having a mental block on how to accomplish this and appreciate any help. Linda Here's one approach... Add a combobox (unbound) to the form (I stick mine in the header). Have that combobox list the records available for editing. I usually only include the bare minimum necessary to distinguish one record from another. Use a query to "feed" t...

Serializing existing inventory
I've convinced the people who matter that we need to go to serialized inventory. I have LOTS of items in my SO & HQ databases that I need to convert from Standard to Serialized item type. Here's what I have in mind--you tell me what I've missed... 1. Identify items to be changed from Standard to Serialized. Easy. I will use a Symbol PocketPC scanner and PTS' Tracer software to scan the Item Lookup Codes and Serial Numbers from most of the items. Floor models will have to be entered manually, but overall they're a small percentage of the entire inventory. 2. R...

email user does not exist that does exist
I have an exchange 2003 server with multiple email domains. some users are not part of the other domains, so they don't have smtp addresses that relate to them, including the primary domain of the server. problem is that after a while, (hours) the users in the other domains seem to disappear from the rest of the world. they are still in active directory, but when someone tries to send a message to them, they get a ndr telling them that the user doesn't exist. any ideas? human error, disregard... human error, disregard... ...

Counter/Incrementing
I have a cell that we increment by one every day. For example: Today the number is: 1 Tomorrow we will set the number to: 2 Day after tomorrow it will be: 3 and so on... The cell doesn't 'count' anything, it's just a number that increments by one with each new day. Any formulas that will automate this? If the couter starts on the 01 may 04: =TODAY()-38108 and format as number or general. Dunca -- Message posted from http://www.ExcelForum.com Hi M, try =TODAY()-38117 Format the cell as general HTH >-----Original Message----- >I have a cell that we increment b...

CRM 3.0 Setup err databases already exist on the specified SQL Ser
ReportServer ReportServerTempDB Question : How to clear this problem? The On-Line Help did not applied as SQL 2005 has been installed on SBS2003 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Microsoft SQL Server Reporting Services Cause There are two causes for this error: Microsoft SQL Server Reporting Services is already installed on the specified server. Setup has found a version of Microsoft SQL Server that is not supported by Microsoft SQL Server Reporting Services. Solution One of the following versions of Microsoft SQL Server are required for Microsoft SQL Server Repor...

Module to check folder existance
How to wite a module to check folder " Report " exist or not. If it doesn't exist ,then vba create one. Later I need to keep my access pdf report to this folder before attach it to my email. thank's See: FileExists() and FolderExists() functions at: http://allenbrowne.com/func-11.html If the folder does not exist, use MkDir to create it. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rahmad" <chooriang@gmail.com> wrote ...

Deduping with Linq / Where Exists
Here is a Tsql sample to query a table for listing 2 employees from each department. How would this look using Linq if I pulled the data contents below to a dataTable in my C# app? Note: the Emp column is unique create table #tmp1 (Emp int unique, Dept varchar(5)) insert into #tmp1 select 1, 'a' union all select 2, 'a' union all select 3, 'a' union all select 4, 'a' union all select 5, 'a' union all select 6, 'b' union all select 7, 'b' union all select 8, 'b' union all select 9, 'b' union all ...

adding a business contact to an existing account
Whenever I try to add a new business contact to an existing account, I get the following message: "The form required to view this message cannot be displayed. Contact your administrator." BCM sometimes has what's known as a "forms cache" problem. Clearing the cache usually fixes it. Choose Tools | Options | Other | Advanced Options | Custom Forms, then click Manage Forms, then Clear Cache. If simply clearing the cache doesn't fix the issue, try the other steps detailed at http://support.microsoft.com/default.aspx?scid=kb;en-us;836558 The bulk of the forms ...

Run Rules on existing inbox
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3346077206_814772 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit I have finally gotten around to clearing my inbox and creating rules to keep it that way the question is, once I have created some rules, (which I have) can I get them to run over the current inbox and sort? As apposed to manually sorting the curent inbox and waiting for new mail to be sorted b my new rules. Thanks --B_3346077206_...

selecting and inserting existing and non existing rows into table parameters and variables
I need to do the following: 1. Select all rows that exist both in the TVP @Headlines and in the table [Headlines].[Headlines] and insert them into a table variable @ExistingHeadlines. 2. Select the rows that exist only in the TVP @Headlines and not in [Headlines].[Headlines] and insert them into a table variable @NonexistentHeadlines. How would you do this? The stored procedure takes 2 totally different paths depending on whether or not the items in @Headlines already exist in the main Headlines table. 1. If they exist already, return an error telling the client that thes...

Rename Existing Distribution Lists in Bulk
There are many distribution lists name DL - *Something or DL - **Something. This is in the DisplayName and Exchange Alias. I would like to know if there is a script that would allow be to modify the DisplayName and Exchange Alias of these DL's? Any help is appreciated. ...

Deployment Package checking to see if file exists
Hi i wonder if someone can help me. Im creating a deployment package for my program. On install i want to check to see if config.xml in my application folder exists if it does then dont overite it. How can i configure the deployment package to do that? Im using visual studio 2005 In VS setups you can't. You can search for a file (in the Launch Conditions view), but that happens before the install, and you don't know what the application folder is until after the user has chosen a folder. Anyway, what will you do if the user chooses a different install location? There wo...

Does this qsbridge property exist?
I have an add-in that uses a web page and to get more screen real estate I had the totals and function keys. When they post from this page it shows them again. The problem comes in when I escape from the page these items are still off. Ideally, I'd like to determine if the user has these visible and then I can get the status html to turn it back on. I do not see a property that determines if these items are currently visible. Does one exist? ...

User does no exist to you
When Ianyone in my company attempts to email to a certain user or users at one domain in practicualr we get back a user does not exist to you error message. What could be causing this and can I resolve this. I've verified that the address is valid. They are able to email us but we cannot email them. In news:9AC8890B-91AD-403B-BF70-C4433F5B5335@microsoft.com, George Schneider <georgedschneider@news.postalias> typed: > When Ianyone in my company attempts to email to a certain user or > users at one domain in practicualr we get back a user does not exist > to you error ...

How to Edit the Macro that exists behind an existing Command Butto
Hi There, Hoping someone can help me out ... I have inherited the task of managing an Access 2007 application. Within this application I have an Access 2007 form that has a Command Button. I'm not sure how this Command Button was created (Macro, Query, etc.) but I do know what it does. I would like to edit the Query/Macro this Command Button executes. I'd rather not edit the VBA coding as I am a VBA Beginner. Can you pleae let me know how I can edit the Command Button's Query/Macro. Thank you, Jason A command button can run a macro or a VB event proce...

Assign Contact to existing Account
There doesn't appear to be any way to assign an existing contact to an account and have it appear in the account's contacts listing on the side. I know that you can create a relationship, but that is not acceptable. Am I missing something, or is this obvious functionality that is missing? Simon Just change the Parent Customer on the contact to point to the account. -- Matt Parks MVP - Microsoft CRM "Simon" <Simon@discussions.microsoft.com> wrote in message news:4A01CC8E-6A19-46B8-BA8C-A557B76C48CA@microsoft.com... There doesn't appear to be any way to assig...

Cannot edit existing file
Hi! I got a pub-file that I need to get some graphics from. As I understand, there are no alternatives than to download Microsoft Publisher, I downloaded the trial version. But Publisher does not allow me to do anything with the file, except look at it! Absolute everything is "grayed out" (except a few things not related to editing or saving the file). How can I get the content from the file? Using a screen grabber seems like a stupid move. So how can actually use Publisher to something? I hardly think that the trial version only allows to view existing files... Thanks alot...

what other charting software exists?
I am frustrated with Excel's limited charting ability. To produce many effects, you must fragment your data and trick Excel with it, and to my mind this makes the data table confusing to anyone who might look at it apart from the creator, and also essentially eliminates the ability to change data in the table and see the change immediately on the chart. I understand other software may be used for charting, but I haven't been able to find anything on Google because apparently the word "charting" usually refers to the charting of stocks, so all the pages I find refer to softwa...

Set up alias email address for existing user
Hello I am new to exchange 2003, and I need to set up 2 alias address for sales@company.com and support@company.com to be forwarded to user@company.com. I tried to setting up a contact for sales@comapny.com, but then I couldn't forward that to user@company.com. How do I get this done? Thanks in advance for the assist. Aloha newbee100, Just go to Active Director Users & Computers, find the user for user@company.com, open their properties and go to the e-mail addresses tab. You can add the additional addresses there. -Ben- Ben M. Schorr - MVP http://www.rolandschorr.com Micr...