Quickly accessing data from SQL server

Hello,
I'm wondering how quickly accessing the following data from a SQL server
database:
- i have a table called "Employee" with several employees in it
- i have a table called "Contract" with one or more contracts per employee
- i have a table called "VisitDirect" with one or more direct visits per
contract - employee
- i have a table called "VisitIndirect" with one or more indirect visits per
contract - employee
Now I want to be able to receive all visits for employees - contracts within
a certain period, depending on the status of the individual visits. So, if
minimum one visit fits a certain criterium, i want to see all visits for tha
t
employee - contract.
So i thought it would be nice to make a query that tracks all employees -
contracts where there is minimum a visit fitting the criterium and than usin
g
that query as a subquery to receive all visits for those employees -
contracts. However, I have to receive the visits from two tables, knowing
VisitDirect and VisitIndirect. So the subquery has to be excecuted twice an
d
i'm afraid it will cost to much time to execute.
Is there any way to access this data on a quicker / smarter way?
Thanks!Hi
If you feel a query is calling multiple times and thats going to hit the
performance, then u can concider using a VIEW.
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"StevenVints" wrote:

> Hello,
> I'm wondering how quickly accessing the following data from a SQL server
> database:
> - i have a table called "Employee" with several employees in it
> - i have a table called "Contract" with one or more contracts per employee
> - i have a table called "VisitDirect" with one or more direct visits per
> contract - employee
> - i have a table called "VisitIndirect" with one or more indirect visits p
er
> contract - employee
> Now I want to be able to receive all visits for employees - contracts with
in
> a certain period, depending on the status of the individual visits. So, i
f
> minimum one visit fits a certain criterium, i want to see all visits for t
hat
> employee - contract.
> So i thought it would be nice to make a query that tracks all employees -
> contracts where there is minimum a visit fitting the criterium and than us
ing
> that query as a subquery to receive all visits for those employees -
> contracts. However, I have to receive the visits from two tables, knowin
g
> VisitDirect and VisitIndirect. So the subquery has to be excecuted twice
and
> i'm afraid it will cost to much time to execute.
> Is there any way to access this data on a quicker / smarter way?
> Thanks!|||hi steve
why do u have to check the twice:
u can directly query in one go. If there are entries then the
rows are returned. else nothing would happen
in you case u can do as:
SELECT direct visit
UNION
SELECT indirect visit
If there are no appointments, no rows would be returned
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"StevenVints" wrote:

> Hello,
> I'm wondering how quickly accessing the following data from a SQL server
> database:
> - i have a table called "Employee" with several employees in it
> - i have a table called "Contract" with one or more contracts per employee
> - i have a table called "VisitDirect" with one or more direct visits per
> contract - employee
> - i have a table called "VisitIndirect" with one or more indirect visits p
er
> contract - employee
> Now I want to be able to receive all visits for employees - contracts with
in
> a certain period, depending on the status of the individual visits. So, i
f
> minimum one visit fits a certain criterium, i want to see all visits for t
hat
> employee - contract.
> So i thought it would be nice to make a query that tracks all employees -
> contracts where there is minimum a visit fitting the criterium and than us
ing
> that query as a subquery to receive all visits for those employees -
> contracts. However, I have to receive the visits from two tables, knowin
g
> VisitDirect and VisitIndirect. So the subquery has to be excecuted twice
and
> i'm afraid it will cost to much time to execute.
> Is there any way to access this data on a quicker / smarter way?
> Thanks!|||Thanks for your quick response!
What if i need only certain records from the view, depending on an startdate
and an enddate. Is a view still quicker than using the query itself? For
example:
select * from table1
where field1 in (select field from table3 where date between startdate and
enddate)
union
select * from table2
where field2 in (select field from table3 where date between startdate and
enddate)
this can also be solved with a view:
select * from table1
where field1 in (select field from view where date between startdate and
enddate)
union
select * from table2
where field2 in (select field from view where date between startdate and
enddate)
What's the advantage of using a view here?
"Chandra" wrote:
> Hi
> If you feel a query is calling multiple times and thats going to hit the
> performance, then u can concider using a VIEW.
> Please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "StevenVints" wrote:
>|||Hi
There is no advantage in using a view in the given example.
It would be advantageous if the view was build from the query
select field from table3 where date between startdate and enddate
the query should have been rewritten as:
select * from table1
where field1 in (select field from view)
union
select * from table2
where field2 in (select field from view)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"StevenVints" wrote:
> Thanks for your quick response!
> What if i need only certain records from the view, depending on an startda
te
> and an enddate. Is a view still quicker than using the query itself? For
> example:
> select * from table1
> where field1 in (select field from table3 where date between startdate and
> enddate)
> union
> select * from table2
> where field2 in (select field from table3 where date between startdate and
> enddate)
> this can also be solved with a view:
> select * from table1
> where field1 in (select field from view where date between startdate and
> enddate)
> union
> select * from table2
> where field2 in (select field from view where date between startdate and
> enddate)
> What's the advantage of using a view here?
>
> "Chandra" wrote:
>