get 5 rows from 2 unrelated tables (was "Complicated sql query.... need help bad

I am trying to write a query that queries 2 tables and gets back 5 rows. These two tables arent really related and the data I get back is different for either table.
The 5 rows that I want to get back are for the LATEST 5 DATES. The date field for one table (F_INSPECTIONS) is END_DATE and the date field for the other table (F_OCCURRENCES) is OCCURRRENCE_DATE.

I am writing a program to do this so if its absolutely impossible to implement this with sql code then a suggestion of how I might be able to go about doing it antoher way would help.

Table descriptions:

/****** Object: Table [dbo].[F_INSPECTIONS] Script Date: 2/8/2005 10:59:41 AM ******/
CREATE TABLE [dbo].[F_INSPECTIONS] (
[INSPECTION_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
[INSPECTION_NAME] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[CAP_FACILITY_ID] [int] NOT NULL ,
[REG_SURR_ID] [smallint] NOT NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[INSP_UPDATED_ON] [datetime] NULL ,
[INSP_ORIGIN_ID] [tinyint] NULL ,
[INSP_TYPE_ID] [tinyint] NULL ,
[DAYS_SINCE_LAST] [smallint] NULL ,
[VIOLATION_COUNT] [smallint] NULL ,
[NON_COMPLIANCE_IND] [tinyint] NULL ,
[INSPECTION_COUNT] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[F_OCCURRENCES] (
[OCCURRENCE_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
[CAP_FACILITY_ID] [int] NOT NULL ,
[OCCURRENCE_NM] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[OCCURRENCE_DATE] [datetime] NULL ,
[REG_SURR_ID] [smallint] NOT NULL ,
[REPORTED_DATE] [datetime] NULL ,
[ASSESSMENT_DATE] [datetime] NULL ,
[UPDATED_ON] [datetime] NULL ,
[ORIGIN_ID] [tinyint] NULL ,
[CATEGORY_ID] [tinyint] NULL ,
[OUTCOME_ID] [tinyint] NULL
) ON [PRIMARY]

I need to query from these 2 tables and get these columns back:Are you trying to get back only 5 rows(attempting to join 2 unrelated tables), or 5 rows from each table(candidate for a UNION)?|||If I understood well, you'll need something like this: first "join" similar results from both tables using the UNION, and then select first five of them.SELECT ID, dat
FROM (SELECT ins_id ID, end_date dat
FROM F_INS
UNION
SELECT occ_id ID, occ_date dat
FROM F_OCC
ORDER BY 2)
WHERE ROWNUM <= 5
ORDER BY 2;Selecting "top 5" rows can be done in different ways; this one (using the ROWNUM pseudocolumn) is used in Oracle. I *think* MySQL has "SELECT TOP 5 ..." statement; I don't know which database engine you use (is it Access?), but I hope my example will help you find the result.|||This is exacly the logic I was looking for (top 5 dates out of the entire set) Hopefully a union like this works in SQL SERVER.

Thanks so much.|||My translation of the Oracle syntax into SQL-92 (which should run on MS-SQL 2000) would be:SELECT TOP 5 ID, dat
FROM (
SELECT ins_id ID, end_date dat
FROM F_INS
UNION SELECT occ_id ID, occ_date dat
FROM F_OCC) AS A
ORDER BY 2-PatP|||Here is the statement I wrote using that same logic. It works great however. i cant get the top 5 to work.

(select
F_OCCURRENCES.CAP_FACILITY_ID,
F_OCCURRENCES.REG_SURR_ID as reg_surr_id,
'N/A' as INPECTOR,
'N/A' as COMPLIANCE_STATUS,
'OCCURRENCE' as ACTIVITY,
D_OCCURRENCE_OUTCOME.OUTCOME_ENG_DESC as OUTCOME,
D_REGULATION.REG_ENGLISH_DESC AS REGULATION,
F_OCCURRENCES.OCCURRENCE_DATE as theDATE
from F_OCCURRENCES INNER JOIN D_OCCURRENCE_OUTCOME ON F_OCCURRENCES.OUTCOME_ID = D_OCCURRENCE_OUTCOME.OUTCOME_ID INNER JOIN D_REGULATION ON D_REGULATION.REG_SURR_ID = F_OCCURRENCES.REG_SURR_ID where cap_facility_id = '11518' and F_OCCURRENCES.REG_SURR_ID = '101'
UNION
SELECT
F_INSPECTIONS.CAP_FACILITY_ID,
F_INSPECTIONS.REG_SURR_ID as reg_surr_id,
'NO DATA' as INSPECTOR,
CASE Non_COMPLIANCE_IND WHEN 1 THEN 'Non-Compliant' WHEN 0 THEN 'Compliant' END as COMPLIANCE_STATUS,
'INSPECTION' as ACTIVITY,
DISP_ENGLISH_DESC as OUTCOME,
D_REGULATION.REG_ENGLISH_DESC AS REGULATION,
F_INSPECTIONS.START_DATE as theDATE
FROM F_INSPECTIONS LEFT JOIN F_VIOLATIONS ON F_INSPECTIONS.INSPECTION_ID = F_VIOLATIONS.INSPECTION_ID Left Join D_DISPOSITION on D_DISPOSITION.VIOLATION_DISP_ID = F_VIOLATIONS.VIOLATION_DISP_ID LEFT JOIN D_REGULATION ON F_INSPECTIONS.REG_SURR_ID = D_REGULATION.REG_SURR_ID where F_INSPECTIONS.REG_SURR_ID = '101' and F_INSPECTIONS.CAP_FACILITY_ID = '11518'
)
order by theDate desc|||This is just a "shot in the dark" since I'm not willing to take the time to analyze this SQL at the moment, but if that SQL works then I'd suggest:SELECT TOP 5 *
FROM (
SELECT
F_OCCURRENCES.CAP_FACILITY_ID
, F_OCCURRENCES.REG_SURR_ID as reg_surr_id
, 'N/A' as INPECTOR
, 'N/A' as COMPLIANCE_STATUS
, 'OCCURRENCE' as ACTIVITY
, D_OCCURRENCE_OUTCOME.OUTCOME_ENG_DESC as OUTCOME
, D_REGULATION.REG_ENGLISH_DESC AS REGULATION
, F_OCCURRENCES.OCCURRENCE_DATE as theDATE
FROM F_OCCURRENCES
INNER JOIN D_OCCURRENCE_OUTCOME
ON F_OCCURRENCES.OUTCOME_ID = D_OCCURRENCE_OUTCOME.OUTCOME_ID
INNER JOIN D_REGULATION
ON D_REGULATION.REG_SURR_ID = F_OCCURRENCES.REG_SURR_ID
WHERE cap_facility_id = '11518'
and F_OCCURRENCES.REG_SURR_ID = '101'
UNION SELECT
F_INSPECTIONS.CAP_FACILITY_ID
, F_INSPECTIONS.REG_SURR_ID as reg_surr_id
, 'NO DATA' as INSPECTOR
, CASE Non_COMPLIANCE_IND
WHEN 1 THEN 'Non-Compliant'
WHEN 0 THEN 'Compliant'
END as COMPLIANCE_STATUS
, 'INSPECTION' as ACTIVITY
, DISP_ENGLISH_DESC as OUTCOME
, D_REGULATION.REG_ENGLISH_DESC AS REGULATION
, F_INSPECTIONS.START_DATE as theDATE
FROM F_INSPECTIONS
LEFT JOIN F_VIOLATIONS
ON F_INSPECTIONS.INSPECTION_ID = F_VIOLATIONS.INSPECTION_ID
LEFT JOIN D_DISPOSITION
on D_DISPOSITION.VIOLATION_DISP_ID = F_VIOLATIONS.VIOLATION_DISP_ID
LEFT JOIN D_REGULATION
ON F_INSPECTIONS.REG_SURR_ID = D_REGULATION.REG_SURR_ID
where F_INSPECTIONS.REG_SURR_ID = '101'
and F_INSPECTIONS.CAP_FACILITY_ID = '11518'
) AS z
ORDER BY theDate desc-PatP|||Brilliant!
the "as z" is what made this work.

thanks very much|||That need for an alias bytes a lot of people... Standard SQL-92 isn't always as forgiving as Oracle (or any other vendor) can be, but that's the price you pay for portability.

-PatPsql

Gererating Dynamic Queries During Run Time

How to Gererating Dynamic Queries During Run Time and execute the results

Thanks in advance

Suresh

You will have to use dynamic SQL.

I highly recommend that you review these articles -they will guide you, and they will explain the cautions and dangers.

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

sql

Generating overnight data vs Live - suggestions needed.

We have an MIS system which has approx 100 reports. Each of these
reports can take up to several minutes to run due to the complexity of
the queries (hundreds of lines each in most cases). Each report can be
run by many users, so in effect we have a slow system.

I want to seperate the complex part of the queries into a process that
is generated each night. Then the reports will only have to query
pre-formatted data with minimal parameters as the hard part will have
been completed for the users when they are not in. Ideally we will
generate (stored procedure possibly) a set of data for each report and
hold this on the server. We can then query with simpler parameters
such as by date and get the data back quite quickly.

The whole process of how we obtain the data is very complex. There are
various views which gather data from the back office system. These are
very complex and when queries are run against them including other
tables to bring in more data, it gets nicely complicated.

The only problem is that the users want to have access to LIVE data
from the back office system, specifically the Sales team who want to
access this remotely. My method only allows for data from the night
before, so is there an option available to me which will allow me to
do this ? The queries can't be improved on an awful lot, so they will
take as long as they take. The idea of running them once is the only
way I can see to improve the performance in any significant way.

True I could just let them carry on as they are and let them suffer
with the performance on live data, but I'd like to do something to
improve the situation for them.

Any advice would be appreciated.

Thanks

RyanYep, that definitely makes a difference. Taken me a while to get back
to this, but we're looking at a solution along these lines.

By taking a copy of the data from the view into a table and then
replicating this onto another database (for generating) and then
running stored procedures (from each complex query) against this,
we've been able to reduce the time from 5 minutes for a test generate
query to about 3 seconds. We've timed the other parts and can probably
generate all of our data in less than 5 minutes for 100 complex
queries that would normally take hours.

Once this data is generated we'll simply swap it for the current data
which should be pretty quick. Our report now loads in less than a
second as opposed to 5 mins or so.

This means we can probably update the data once an hour and push it
onto the query database which will be more than quick enough for the
users. This has the knock on effect of being able to produce the
reports in seconds instead of minutes.

Erland, thanks for the advice on this it should make quite a
difference.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93DEF27F53F61Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > The only problem is that the users want to have access to LIVE data
> > from the back office system, specifically the Sales team who want to
> > access this remotely. My method only allows for data from the night
> > before, so is there an option available to me which will allow me to
> > do this ? The queries can't be improved on an awful lot, so they will
> > take as long as they take. The idea of running them once is the only
> > way I can see to improve the performance in any significant way.
> If users want current data, you cannot pre-compute it for them. You
> know, have the cake and eat it.
> What you could to do is to set up a report server which you replicate
> to, so that you take the load of the back-office system.
> Yet another alternative, is to have three databases:
> A - the source-system.
> B - the view system that the users use.-
> C - a computation system.
> You keep the computation database updated by means of log shipping
> (this is better than replication in this case). One you have applied
> a log, you pre-compute all data. When this step is done, you flush the
> pre-computed tables on B, and insert the data from C. Now you go and
> pick up a new log from A. A more fancy variant is to have a seamless
> switch between B and C which you flip constantly. I believe there are
> such things.|||Ryan (ryanofford@.hotmail.com) writes:
> By taking a copy of the data from the view into a table and then
> replicating this onto another database (for generating) and then
> running stored procedures (from each complex query) against this,
> we've been able to reduce the time from 5 minutes for a test generate
> query to about 3 seconds. We've timed the other parts and can probably
> generate all of our data in less than 5 minutes for 100 complex
> queries that would normally take hours.

300 seconds down to three! That's not bad!

> Erland, thanks for the advice on this it should make quite a
> difference.

I'm only glad to have helped! And thanks for reporing back!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Generated query

Hello,

i would like to know where can i see queries which are generated by the cube broswer in visual studio 2005 and where can i see what are the actions it does? Because it takes a very long moment between changes in the browser.

thanks

Start a trace on Analysis Services using the Profiler (assuming that you are using Analysis Services 2005). Then you will be able to see the MDX queries generated by the cube browser.|||thanks a lot

Generate report from list of parameters?

I have one report with two datasets, and consequently two queries. The first
one gets a list of ReportID's for a particular day, while the second dataset
uses each ReportID to create a report.
Query 1:
SELECT ReportID
FROM Table1
WHERE Date=@.Date
Query 2:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID = @.ReportID
If I try to Union '(All)' to Query1, Query2 will return info for all
ReportID's in the system, not just what was generated in Query1. I also
tried creating a comma delimited field of all the ReportID's and using WHERE
ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
Query2 while only running Query1 once?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1On Feb 28, 6:28 am, "ana9" <u28955@.uwe> wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
If I'm understanding you correctly, you should be able to do one of a
few things:
Option 1:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID in (SELECT ReportID
FROM Table1
WHERE Date=@.Date )
Option 2:
Create a cursor (or while loop) to loop through Query 1 and execute
Query 2 in the loop for each ReportID (possibly populating a single
dataset or multiple datasets).
Ofcourse Option 1 is far less performance intensive. If I'm not
understanding you correctly, let me know.
Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||That was very helpful, thank you. Option1 is a variation of what I had tried
before. The only issue (which I failed to mention) is that Query2 is
actually a report that gets populated from about 20 datasets. So each
dataset may not return the exact same number of rows, in which case some of
the information will not be displayed in the correct manor. Also, for some
of those datasets I use SELECT Top # so using Option1 will not allow me to
get the correct amount of results.
Your suggestion for Option2 seems like it would be perfect for this scenario,
however I was under the impression that you can't create while loops in SQL.
Could you point me in the direction of some resources/examples? Thank you.
EMartinez wrote:
>> I have one report with two datasets, and consequently two queries. The first
>> one gets a list of ReportID's for a particular day, while the second dataset
>[quoted text clipped - 18 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>If I'm understanding you correctly, you should be able to do one of a
>few things:
>Option 1:
>SELECT *
>FROM *A whole bunch of tables*
>WHERE ReportID in (SELECT ReportID
>FROM Table1
>WHERE Date=@.Date )
>Option 2:
>Create a cursor (or while loop) to loop through Query 1 and execute
>Query 2 in the loop for each ReportID (possibly populating a single
>dataset or multiple datasets).
>Ofcourse Option 1 is far less performance intensive. If I'm not
>understanding you correctly, let me know.
>Hope this is helpful.
>Regards,
>Enrique Martinez
>Sr. SQL Server Developer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1|||You might also try
SELECT ReportID INTO #Reports
FROM Table1
WHERE Date=@.Date
SELECT *
FROM *A whole bunch of tables*
inner join #Reports
On ReportID = #Reports.ReportID
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ana9" wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>

Generalized perfomance problems with long running queries

Hi,
My problems with SQL Server 2000 SP4 multiplies. Long running queries are
blocking each other (even themselves); i.e.:
SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type. I'm
going to review the queries and database options in order to optimize them
(new indexing, bulk inserting, constraints disabling, frequent checkpointing
,
simple model restore, etc), but... is there a "brute force" solution for
this? (not increasing hardware resources, please; a 4 CPU Dell computer with
4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5 Mil.
rows tables, I believe).
Anyone can help, please?
Luis FernándezOn 25.08.2006 17:19, Luis Fernndez wrote:
> Hi,
> My problems with SQL Server 2000 SP4 multiplies. Long running queries are
> blocking each other (even themselves); i.e.:
> SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
> I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type. I
'm
> going to review the queries and database options in order to optimize them
> (new indexing, bulk inserting, constraints disabling, frequent checkpointi
ng,
> simple model restore, etc), but... is there a "brute force" solution for
> this? (not increasing hardware resources, please; a 4 CPU Dell computer wi
th
> 4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5 Mi
l.
> rows tables, I believe).
The first thing I'd probably check is memory settings, just to make sure
your 2k doesn't hit a 128MB limit.
I'd do a profiler trace of a typical workload (a day, a week whatever)
which includes execution plans. Then check execution plans of long
runners. You can even feed the trace to the ITW but look carefully at
recommendations.
Kind regards
robert|||have you try to reduce your maxdop option?
(maximum CPU used in parrallel queries)
this reduce the blocking behavior, but also slow down the queries.
review your queries and try to create pre-aggregated tables.
try to force the maxdop option per query. you can also force SQL Server in
these queries to not change the join order
by default SQL Server optimize the queries and choose the best plan; but
this plan is not always the best choice.
so you can manually choose how the joins will be used (hash join or merge
join...) and in which order (join 2 tables first, then the third you want
etc...)
can you describe your disk subsystem?
during the execution of these queries, how is your disk activity?
"Luis Fernndez" <LuisFernndez@.discussions.microsoft.com> wrote in message
news:714859AD-B6BF-40F0-853C-C935D1129C9D@.microsoft.com...
> Hi,
> My problems with SQL Server 2000 SP4 multiplies. Long running queries are
> blocking each other (even themselves); i.e.:
> SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
> I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type.
> I'm
> going to review the queries and database options in order to optimize them
> (new indexing, bulk inserting, constraints disabling, frequent
> checkpointing,
> simple model restore, etc), but... is there a "brute force" solution for
> this? (not increasing hardware resources, please; a 4 CPU Dell computer
> with
> 4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5
> Mil.
> rows tables, I believe).
> Anyone can help, please?
> Luis Fernndez