Need SQL Performance Advice (Scenario Given)

Hey guys, having a bit of a urgency here. I need to make certain queries faster in our application, lemme give you a scoup. One of the queries i need to optimize or find a better way to do is the current scenario. We have jobs in our system, these jobs have applicants, some of them are direct applicants, some are matches (from searches), and some are applicants with resumes only.
Some of our clients have 100+ jobs, when they see the first page we list all the jobs with 6 counts next to them:

Job Title, Total Applicants, New Applicants, Total Matches, New Matches, Total Resumes, New Resumes

So we have 6 counts for each job, say you have 100+ jobs thats a lot of counts. Currently i'm performing a sub query for each of the counts since they represent different data points, the only thing that changes is the WHERE clause.

As you can see this can be very heavy and in some cases (100+ jobs) depending of number of applicants might take up to a minute to finish.

I would like to get some opinions from you guys on how to make this run faster, obviously caching cannot be considered since they employer must see the live data (counts)

Any help is greatly appreciated.indexes on the join columns?|||yes indexes are in place, i dont really thing that they are the problem,

i think the subqueries are killing it since one query needs to be run to get each of the count right? is there any other way to structure this thing?

thanks for your help|||depends

without seeing the subqueries, it's kinda difficult to tell what's wrong or whether an alternative structure is possible|||Without seeing your code, my first suggestion would be to "unwrap" the subqueries. Make a single pass through the data, doing a single join. Reimplement the counts as sums, and use CASE to provide the "smarts" to make it work.

As Rudy pointed out, without seeing what you are doing, we are pretty sorely limited in how we can help. This is kind of like calling someone on another continent and telling them "my stomach hurts" and asking them what you should do about it.

-PatP

Need some help with dropping certain rows in a sql table ****HELP! ****

Hello,
I need to get some help with my T-SQL statements. Basically, I have
the follwing
data in a SQLServer table and it looks something like this:
Current data:
Vendor Item Frequency Price
AA 101 25 10.50
AA 102 10 20.50
AA 103 2 9.75
AA 104 2 10.99
AA 105 1 10.99
BB 101 25 10.50
BB 102 1020.50
BB 103 29.75
BB 104 210.99
BB 105 221.25
BB 106 121.25
Desired result:
Vendor Item Frequency Price
AA 101 25 10.50
AA 102 10 20.50
AA 104 2 10.99
BB 101 2510.50
BB 102 1020.50
BB 105 221.25
The logic basically says: excludes Frequency <= 1, if the Frequency is
equal, then
choose the item which has the highest price and excludes the others
that have the
same frequency. That is it.
I would like to know the result could be achieved with a single T-SQL
statement?
If yes, please show me how. If not, please show your best
alternative.
Thank you in advance!
Here is one way to accomplish this using a single statement (SQL Server
2005):
CREATE TABLE VendorItems (
vendor CHAR(2),
item INT,
frequency INT,
price DECIMAL(12, 2),
PRIMARY KEY (vendor, item));
INSERT INTO VendorItems VALUES ('AA', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('AA', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('AA', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('AA', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('AA', 105, 1, 10.99);
INSERT INTO VendorItems VALUES ('BB', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('BB', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('BB', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('BB', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('BB', 105, 2, 21.25);
INSERT INTO VendorItems VALUES ('BB', 106, 1, 21.25);
;WITH RankedVendorItems
AS
(SELECT vendor, item, frequency, price,
ROW_NUMBER() OVER(
PARTITION BY vendor, frequency
ORDER BY price DESC) AS seq
FROM VendorItems
WHERE frequency > 1)
SELECT vendor, item, frequency, price
FROM RankedVendorItems
WHERE seq = 1
ORDER BY vendor, item;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Need some help with dropping certain rows in a sql table ****

Hello,
I need to get some help with my T-SQL statements. Basically, I have
the follwing
data in a SQLServer table and it looks something like this:
Current data:
Vendor Item Frequency Price
----
AA 101 25 10.50
AA 102 10 20.50
AA 103 2 9.75
AA 104 2 10.99
AA 105 1 10.99
BB 101 25 10.50
BB 102 10 20.50
BB 103 2 9.75
BB 104 2 10.99
BB 105 2 21.25
BB 106 1 21.25
----
Desired result:
Vendor Item Frequency Price
----
AA 101 25 10.50
AA 102 10 20.50
AA 104 2 10.99
BB 101 25 10.50
BB 102 10 20.50
BB 105 2 21.25
----
The logic basically says: excludes Frequency <= 1, if the Frequency is
equal, then
choose the item which has the highest price and excludes the others
that have the
same frequency. That is it.
I would like to know the result could be achieved with a single T-SQL
statement?
If yes, please show me how. If not, please show your best
alternative.
Thank you in advance!Here is one way to accomplish this using a single statement (SQL Server
2005):
CREATE TABLE VendorItems (
vendor CHAR(2),
item INT,
frequency INT,
price DECIMAL(12, 2),
PRIMARY KEY (vendor, item));
INSERT INTO VendorItems VALUES ('AA', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('AA', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('AA', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('AA', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('AA', 105, 1, 10.99);
INSERT INTO VendorItems VALUES ('BB', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('BB', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('BB', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('BB', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('BB', 105, 2, 21.25);
INSERT INTO VendorItems VALUES ('BB', 106, 1, 21.25);
;WITH RankedVendorItems
AS
(SELECT vendor, item, frequency, price,
ROW_NUMBER() OVER(
PARTITION BY vendor, frequency
ORDER BY price DESC) AS seq
FROM VendorItems
WHERE frequency > 1)
SELECT vendor, item, frequency, price
FROM RankedVendorItems
WHERE seq = 1
ORDER BY vendor, item;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Need some help to understand T-SQL vs 'official' SQL standards

Hello,

I would like to know if a certain T-SQL function is actually in an official standard (today the ISNULL function, but in the future other functions as well). Is there a chart somewhere or another way to look this up?

Thank you!

The best resource I know of is the Mimer SQL validator ( http://developer.mimer.com/validator/parser200x/index.tml ). You can put your query there to see if there are any standards-compliance issues.

-Ryan / Kardax

|||Did you try a Google? search of, oh, I don't know, perhaps: "ansi AND sql AND functions"?|||Yes, but I was looking more for a resource where you can immediately see that:
function x is in tsql, but it's not in sql92
Best would be a page with all functions. But the concept of Google is not lost on me.
|||I assume you are asking this question because you want to write "SQL standard code".

WARNING: There is NO SUCH THING. Every SQL server implementation has its own "enhancements" and "work arounds" and even differences of "interpetation" of the so-called SQL standard.

If you change database engines, you will have to check all, and probably rewrite many, SQL statements to conform to the new engine. And you will probably want to do this anyway to benefit from the enhancements of the new engine.

The so-called SQL92 standard boils down to: There will be the statement "SELECT" followed by more stuff to return records.

|||

I think that 'owning' a copy of the O'Reilly book would be a good idea. (Fortunately, ANSI standards don't change very often.)

Chapter 4 (SQL Functions) is even available free online.

http://www.oreilly.com/catalog/sqlnut2/chapter/ch04.pdf

Need Query Help for Search

I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox. I want the results to only return results that begin with the letter/letters specified. Below I will put the code that I came up with that isn't working quite how I expected. I am new to this so any assistance and short explanation would be very much apperciated.

sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 "

sql=sql & "FROM cmmst cm "

sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC"

companyNameBegins is the string passed in by the user

Thanks,

Zoop

(1) Use parameterized Queries. Your code will look simpler and neater and you can avoid SQL Injection Attacks (Google for more info on this topic)

(2) Append the "%" in the value rather than in the SQL. Also if you want to retrieve records starting with a value the % should be at the end like "SELECT...WHERE column like 'startwith%'"

Here's some sample code:

Dim myCommand As SqlCommand
Dim myParam As SqlParameter

myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 FROM cmmst cm WHERE cm_cname1 + ' ' + cm_cname2 LIKE @.companyNameBegins AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') ORDER BY cm_s16 DESC, cm_cname1 ASC"

myCommand.Parameters.Add(New SqlParameter("@.companyNameBegins ",SqlDbType.varchar,100))
myCommand.Parameters("@.companyNameBegins").Value = companyNameBegins & "%"

Try
If objCon.State = 0 Then objCon.Open()
'ExecuteReader and fill some dataContainer.

Catch exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End Try

|||

Thanks for the assistance, much smoother this way.

zoop