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 knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen PerssonSteen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen PerssonSteen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I need to get my knowledge about indexes expanded a little bit...
>> What's the best/easiest way to view Indexes and their definition in
>> SQL server. I can look in sysindexes, but as I see it it only shows
>> me the different indexes by name, but not so much about the
>> definition etc. (or maybe I just don't know what to look for). I can
>> also use sp_helpindex, but that only shows me very little as well. I
>> know I can look up the difinition in EM, but that's a bit cumbersome
>> to click through all tables to see it. Reason for asking is that I
>> have 2 almost identical databases, where on one of them there's a
>> number of indexes defined. These Indexes I'd like to create in the
>> second database as well.
>> Best Regards
>> Steen Persson

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen Persson
Steen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>
|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>
|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...

Need some help with query

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Quote:

Originally Posted by alvinguy

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Try to use joins instead of alias names
sample:
3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME from Project b
left join WORKSON c on C.Pnumber=b.Pnumber
left join EMPLOYEE a on a.SSN=c.SSN
WHERE b.PLOCATION !=Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

select distinct Employee.Name from Project
left join Workson on Workson.PNumber=Project.PNumber
left join Employee on Employee.SSN=Workson.SSN
where Project.PLOcation='Houston'

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

Select Name from Employee where SSN=(Select SSN from
(Select SSN,count(SSN) as cnt from
(select distinct SSN,Workson.PNumber from Project
left join Workson on Workson.Pnumber= Project.PNumber
where Project.PLocation='Houston'
group by SSN,Workson.PNumber) as A
group by SSN) as B
where cnt=(select Count(Pnumber) from Project Where Plocation='Houston'))|||

Quote:

Originally Posted by alvinguy

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Hi
Different way for different queries exist.
if u still have problem in 3rd query u can use the following.

SELECT a.NAME
FROM
EMPLOYEE AS a, WORKSON AS b
WHERE a.SSN=b.SSN and b.PNUMBER IN
(SELECT PNUMBER
FROM PROJECT
WHERE PLOCATION NOT IN('HOUSTON'));

IF U STILL HAVE ANY PROBLEM IN ANY OTHER QUERY PLZ TELL I WILL TRY MY BEST TO SOLVE THAT ONE.sql

need some help with my sql query

I'm still learning this stuff and I'm a bit confused. I have 3 tables: products, categories and subcategories. the products table has the foreign key for subcategories and the subcategories table has the foreign key for categories. I want to return a table that has the ProductID, SubCategoryID and CategoryID. I can't seem to fiqure out how to get the categoryID to the results table. I'm not sure if I'm supposed to use joins or subqueries to get the categoryID. All I have so far is this:

sql="SELECT Products.ProductID, Products.SubCategoryID FROM Products"

any help would be appreciated.I've come up with the following, i'm not sure if this is the correct way to do it, but it seems to work. I ran it through SQL Query Analyzer and it appears to give the results that i was expecting.

sql="SELECT Products.ProductID, Products.SubCategoryID, (SELECT CategoryID FROM SubCategories WHERE SubCategoryID = Products.SubCategoryID) as CategoryID
FROM Products";

If there is another/better/correct way to do this please let me know. Thanks.|||Another way to do it, which might be more efficient, is:

sql="SELECT Products.ProductID, Products.SubCategoryID, SubCategories.CategoryID FROM Products LEFT OUTER JOIN SubCategories ON Products.SubCategoryID = SubCategories.SubCategoryID";

Terri|||Very good, thanks. That is exactly what I was looking for.

Need some help A.S.A.P.

Hi Folks,
I'm having a bit of trouble updating my script. I have a
script used for MS Access and I need to transfer it to be
compatible with MS SQL Server. When I run the script it
gives the error
''ODBC driver does not support the requested
properties.''
This is the line of the script
that I have to change to work with access:
''oRS.Open SqlTxt , oConn'' and the sql text is:
''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId
FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;".
Could anybody please help me out in solving this problem?
Cheers!
Hi
Maybe you can conect to the MS SQL database using MS Access? i don't know?
maybe diverent ODBC driver?
I'm still new to this but just trying to gif you some things you may wanna
try.
Greatings,
Crazy Pete
"Will" <anonymous@.discussions.microsoft.com> schreef in bericht
news:2396c01c45ebe$550b2f10$a301280a@.phx.gbl...
> Hi Folks,
> I'm having a bit of trouble updating my script. I have a
> script used for MS Access and I need to transfer it to be
> compatible with MS SQL Server. When I run the script it
> gives the error
> ''ODBC driver does not support the requested
> properties.''
> This is the line of the script
> that I have to change to work with access:
> ''oRS.Open SqlTxt , oConn'' and the sql text is:
> ''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId
> FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;".
> Could anybody please help me out in solving this problem?
> Cheers!
>
sql

Need SMO Resources

Hi,
Could you please point me to some deep SMO resources : articles, books,
e-books ...
I've google a little bit and just find some basic stuff.
Thank youHeve you seen this?
http://download.microsoft.com/downl...%20services.doc
ML

Need more table rows displayed on page

Right now I have a report with groupings and each full set takes up quite a
bit of room. So right now it only displays 10 sets per page, but I'd like to
increase this to 30. How can I make a table display more information before
making a new page?Change the page size... It is in the report properties menu item...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
> Right now I have a report with groupings and each full set takes up quite
> a
> bit of room. So right now it only displays 10 sets per page, but I'd like
> to
> increase this to 30. How can I make a table display more information
> before
> making a new page?|||Thanks, ended up being a small fix. I'd rather there be some type of exact
number since now it can differ from page to page since some entries are
larger/smaller, but this works fine.
"Wayne Snyder" wrote:
> Change the page size... It is in the report properties menu item...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
> news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
> > Right now I have a report with groupings and each full set takes up quite
> > a
> > bit of room. So right now it only displays 10 sets per page, but I'd like
> > to
> > increase this to 30. How can I make a table display more information
> > before
> > making a new page?
>
>|||I have an example on www.msbicentral in the RDL Downloads section which
shows how to set a fixed number of rows/page to display... Its not very
hard...The filename is PagedTableParameters.RDL
Hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
news:F9921495-BF23-42A9-ADB6-AB5FB3319188@.microsoft.com...
> Thanks, ended up being a small fix. I'd rather there be some type of exact
> number since now it can differ from page to page since some entries are
> larger/smaller, but this works fine.
> "Wayne Snyder" wrote:
>> Change the page size... It is in the report properties menu item...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
>> news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
>> > Right now I have a report with groupings and each full set takes up
>> > quite
>> > a
>> > bit of room. So right now it only displays 10 sets per page, but I'd
>> > like
>> > to
>> > increase this to 30. How can I make a table display more information
>> > before
>> > making a new page?
>>

Need Kick in right direction w/query take 2

Bumping this up to the top, scroll down a bit to see the original thread.
--
I have updated the DDL with the actual tables and a sample of table data of
what I am actually using.
http://damageinc.org/DDL.html (The DDL is too large to post, the message
gets kicked back to me)
AFter you enter that DDL
If you run the query..
SELECT Apps.AppName, Tests.TestCase, TestCases.Type
FROM
TestCases
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
LEFT OUTER JOIN Tests ON TestCases.TestID = Tests.ID
WHERE
(
(
(Proj1 = '87' OR Proj2 = '87' OR Proj3 = '87' OR Proj4 = '87' OR Proj5 =
'87')
OR
(Proj1 = '88' OR Proj2 = '88' OR Proj3 = '88' OR Proj4 = '88' OR Proj5 =
'88')
)
AND (TestCases.Card = 'G71_D' OR TestCases.Card = 'G70_D' OR TestCases.Card
= 'G72_D' OR TestCases.Card = 'G73_D')
AND TestCases.OS = 'Windows Vista'
)
GROUP BY AppName, TestCase,TestCases.Type
You will see
AppName TestCase
Type
3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso D3D Benchmarks
3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso Games
3D Mark 2003 Benchmark: Default 2x AA 4x Aniso D3D
Benchmarks
3D Mark 2003 Benchmark: Default 2x AA 4x Aniso G ames
.
.
.
.
There are 97 tests there.
What I need is that 97 for the count, then the count of the most recent
pass/fail result(s) (if there is a result) for each of those tests.
Using
Select Count(*) As 'Count',
Sum(Case When Result = 'Pass' Then 1 Else 0 End) As 'Pass',
Sum(Case When Result = 'Fail' Then 1 Else 0 End) As 'Fail'
From Reports r
Where r.ReportDate In
(Select Max(r.ReportDate)
From Reports r
Inner Join TestCases tc On r.TestCaseID = tc.ID
Inner Join Tests t On tc.TestID = t.ID
Inner Join Apps a On tc.AppID = a.ID
WHERE
(
(
(tc.Proj1 = '87' OR tc.Proj2 = '87' OR tc.Proj3 = '87' OR tc.Proj4 = '87'
OR tc.Proj5 = '87')
OR
(tc.Proj1 = '88' OR tc.Proj2 = '88' OR tc.Proj3 = '88' OR tc.Proj4 = '88'
OR tc.Proj5 = '88')
)
AND (tc.Card = 'G71_D' OR tc.Card = 'G70_D' OR tc.Card = 'G72_D' OR tc.Card
= 'G73_D')
AND tc.OS = 'Windows Vista'
)
Group By a.AppName, t.TestCase)
Gives me
Count Pass Fail
25 2 23
Which is obviously wrong, since the Count should be 97.
I have to be overthinking something here...I think this works:
SELECT Count(*) As 'Count',
Sum (Case When r.Result = 'Pass' Then 1 Else 0 End) As 'Pass',
Sum (Case When r.Result = 'Fail' Then 1 Else 0 End) As 'Fail'
From Reports r
Inner Join TestCases tc On r.TestCaseID = tc.ID
LEFT OUTER JOIN Apps a ON tc.AppID = a.ID
LEFT OUTER JOIN Tests t ON tc.TestID = t.ID
Right Join (
SELECT a.AppName, t.TestCase, tc.Type, Max(Coalesce(r.ReportDate,
'1900-01-01'))As ReportDate
FROM TestCases tc
LEFT OUTER JOIN Apps a ON tc.AppID = a.ID
LEFT OUTER JOIN Tests t ON tc.TestID = t.ID
LEFT OUTER JOIN Reports r ON r.TestCaseID = tc.ID
WHERE
(
(
(Proj1 = '87' OR Proj2 = '87' OR Proj3 = '87' OR Proj4 = '87' OR Proj5 =
'87')
OR
(Proj1 = '88' OR Proj2 = '88' OR Proj3 = '88' OR Proj4 = '88' OR Proj5 =
'88')
)
AND (tc.Card = 'G71_D' OR tc.Card = 'G70_D' OR tc.Card = 'G72_D' OR tc.Card
= 'G73_D')
AND tc.OS = 'Windows Vista'
)
GROUP BY AppName, TestCase,tc.Type) x On r.ReportDate = x.ReportDate
Tom
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:%23zfcKfZmGHA.3880@.TK2MSFTNGP02.phx.gbl...
> Bumping this up to the top, scroll down a bit to see the original thread.
> --
> I have updated the DDL with the actual tables and a sample of table data
> of
> what I am actually using.
> http://damageinc.org/DDL.html (The DDL is too large to post, the message
> gets kicked back to me)
> AFter you enter that DDL
> If you run the query..
> SELECT Apps.AppName, Tests.TestCase, TestCases.Type
> FROM
> TestCases
> LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
> LEFT OUTER JOIN Tests ON TestCases.TestID = Tests.ID
> WHERE
> (
> (
> (Proj1 = '87' OR Proj2 = '87' OR Proj3 = '87' OR Proj4 = '87' OR Proj5 =
> '87')
> OR
> (Proj1 = '88' OR Proj2 = '88' OR Proj3 = '88' OR Proj4 = '88' OR Proj5 =
> '88')
> )
> AND (TestCases.Card = 'G71_D' OR TestCases.Card = 'G70_D' OR
> TestCases.Card
> = 'G72_D' OR TestCases.Card = 'G73_D')
> AND TestCases.OS = 'Windows Vista'
>
> )
> GROUP BY AppName, TestCase,TestCases.Type
>
> You will see
>
> AppName TestCase
> Type
> 3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso D3D Benchmarks
> 3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso Games
> 3D Mark 2003 Benchmark: Default 2x AA 4x Aniso D3D
> Benchmarks
> 3D Mark 2003 Benchmark: Default 2x AA 4x Aniso G ames
> .
> .
> .
> .
>
> There are 97 tests there.
> What I need is that 97 for the count, then the count of the most recent
> pass/fail result(s) (if there is a result) for each of those tests.
> Using
> Select Count(*) As 'Count',
> Sum(Case When Result = 'Pass' Then 1 Else 0 End) As 'Pass',
> Sum(Case When Result = 'Fail' Then 1 Else 0 End) As 'Fail'
> From Reports r
> Where r.ReportDate In
> (Select Max(r.ReportDate)
> From Reports r
> Inner Join TestCases tc On r.TestCaseID = tc.ID
> Inner Join Tests t On tc.TestID = t.ID
> Inner Join Apps a On tc.AppID = a.ID
> WHERE
> (
> (
> (tc.Proj1 = '87' OR tc.Proj2 = '87' OR tc.Proj3 = '87' OR tc.Proj4 =
> '87'
> OR tc.Proj5 = '87')
> OR
> (tc.Proj1 = '88' OR tc.Proj2 = '88' OR tc.Proj3 = '88' OR tc.Proj4 =
> '88'
> OR tc.Proj5 = '88')
> )
> AND (tc.Card = 'G71_D' OR tc.Card = 'G70_D' OR tc.Card = 'G72_D' OR
> tc.Card
> = 'G73_D')
> AND tc.OS = 'Windows Vista'
> )
> Group By a.AppName, t.TestCase)
> Gives me
> Count Pass Fail
> 25 2 23
>
> Which is obviously wrong, since the Count should be 97.
> I have to be overthinking something here...
>
>

need information for "Before Select" Trigger

hi guys!
Currently i'm facing a bit odd situation. i cant modify my code so i
have to make changes in SQL Server.

the problem is, i've modified one table by adding new column and now i
want only those row which has not null value in that column whenever
the select statement gets fire. i mean in every select statement only
those rows should be returned which has NOT NULL value in the newly
added column.

for this i've created one view and tried to run the application but the
i'm facing some problem as my application is developed in .net 2.0,
i've used lots of .net functionalities and now i cant make any changes
in Code.

another solution i think is triggers. if i can fire a trigger before
any select statements, my problem can be solved instantly but i'm
afraid i can not write andy "before select" trigger.

if anyone has any idea or any other solution to my problem, please let
me know.

all suggestions are welcomed.

thanks,
LuckyLucky (tushar.n.patel@.gmail.com) writes:
> Currently i'm facing a bit odd situation. i cant modify my code so i
> have to make changes in SQL Server.
> the problem is, i've modified one table by adding new column and now i
> want only those row which has not null value in that column whenever
> the select statement gets fire. i mean in every select statement only
> those rows should be returned which has NOT NULL value in the newly
> added column.
> for this i've created one view and tried to run the application but the
> i'm facing some problem as my application is developed in .net 2.0,
> i've used lots of .net functionalities and now i cant make any changes
> in Code.
> another solution i think is triggers. if i can fire a trigger before
> any select statements, my problem can be solved instantly but i'm
> afraid i can not write andy "before select" trigger.

There are no BEFORE SELECT triggers. (In fact, there are no BEFORE triggers
at all in SQL Server.)

The way to handle this would be define a view, which includs a filter on
this column.

However, personally, I would not add a view for such a simple condition,
but rather add it as needed.

If you real problem is that this table is referred to in lots of places
(lots => more than 20), and you want this column to take effect every
where, you could renamed the table, and then create a view with that
carries on the original name of the table.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Pal,
i did the same way but as i said i'm using .net 2.0's dataset which
seems doesnt understands the view. As i've added "Enforced Constraints"
in the Dataset, may be that could not allowed the View to be considered
with foriegn key, unique value, not null constraint.

to avoid this situation i need to some other way. and yes it is true.
i'm reffering it in more then 20 webpages so i cant modifiy it.

anyways thanks for help.

Lucky

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > Currently i'm facing a bit odd situation. i cant modify my code so i
> > have to make changes in SQL Server.
> > the problem is, i've modified one table by adding new column and now i
> > want only those row which has not null value in that column whenever
> > the select statement gets fire. i mean in every select statement only
> > those rows should be returned which has NOT NULL value in the newly
> > added column.
> > for this i've created one view and tried to run the application but the
> > i'm facing some problem as my application is developed in .net 2.0,
> > i've used lots of .net functionalities and now i cant make any changes
> > in Code.
> > another solution i think is triggers. if i can fire a trigger before
> > any select statements, my problem can be solved instantly but i'm
> > afraid i can not write andy "before select" trigger.
> There are no BEFORE SELECT triggers. (In fact, there are no BEFORE triggers
> at all in SQL Server.)
> The way to handle this would be define a view, which includs a filter on
> this column.
> However, personally, I would not add a view for such a simple condition,
> but rather add it as needed.
> If you real problem is that this table is referred to in lots of places
> (lots => more than 20), and you want this column to take effect every
> where, you could renamed the table, and then create a view with that
> carries on the original name of the table.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lucky wrote:
> Hi Pal,
> i did the same way but as i said i'm using .net 2.0's dataset which
> seems doesnt understands the view. As i've added "Enforced Constraints"
> in the Dataset, may be that could not allowed the View to be considered
> with foriegn key, unique value, not null constraint.
> to avoid this situation i need to some other way. and yes it is true.
> i'm reffering it in more then 20 webpages so i cant modifiy it.
> anyways thanks for help.
> Lucky

I don't think I understand what your problem is but you can obtain the
same effect as a "before" trigger by referring to the virtual table
called Deleted within the trigger. The Deleted table contains the state
of the changed rows before the statement was executed.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Lucky (tushar.n.patel@.gmail.com) writes:
> i did the same way but as i said i'm using .net 2.0's dataset which
> seems doesnt understands the view. As i've added "Enforced Constraints"
> in the Dataset, may be that could not allowed the View to be considered
> with foriegn key, unique value, not null constraint.
> to avoid this situation i need to some other way. and yes it is true.
> i'm reffering it in more then 20 webpages so i cant modifiy it.

Looks like you have to modify 20+ web pages one way or another.

I don't really know how your .Net looks like, but permit me to point
out that had you retrieved the data with stored procedures, the change
would have been snap.

Then again, not even that should be necessary. Had you defined the
SelectCommand for the adapter in one place, it would also have been
a simple thing.

So when you modify your 20+ web pages, you strive to find a strategy
so that it does not happen again.

You may also want to visit microsoft.public.dotnet.framework.adonet for
better advice for the ADO .Net side of things.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need Help. Please

Hi there,
I know it is a bit odd to ask questions on my first posting. I'm new at setting up a MS SQL 2000 Server. I would really appreciate if you can help clarify a few of my questions :) .
I'm considering to buy SQL 2000 Ent. Ed and Windows Server 2003 Ent. Ed. for my two new Servers. I've tried to contact MS customer support, but they are not very helpful :( . I've searched many articles, most of them are not very helpful in helping me make the decision :( .
I would like to set up a cluster with two Windows 2003 Server Ent. Ed. One for active SQL2000 Ent. Ed. and another one for passive. Can this work?
For the Server running the passive SQL 2000. I'll be running a java networking application. I would like to know if the passive windows server 2003 fails, will the Windows Server 2003 be able to handle the failover and transfer my java application to the other server. :confused:
Thanks a lot in advance. Any help is appreciated.

Best Regards,
Keen HonTypically, Microsoft prefers the customer to engage either an OEM or a Microsoft Solutions Provider for this kind of pre-sales support, but that is no reason for them to ignore your request. How did you try to contact customer support?

-PatP|||I called the Customer Care Centre (Microsoft Careline) for my Country in Singapore. I also called some of the Resellers. Maybe I will call the OEM to try my luck.

- KH|||You do know what you're configuring will be a lot of cash...

And you should probably get another box for the app server...

You do know that clustering is NOT for load balancing...right....and don't you want another box for shared Disk?|||Of course your application will be failed over...only if you make it a cluster-aware service.