I'm confused.
I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.
I have inputted data to my database. The user should be able to do searches such as:
rides (and shops or all) available in a certain area.
rides in a certain area above a certain height rescrictions.
etc
I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.
I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...
and what about triggers and user defined statements??
PLEASE help, I have a deadline in a week : (
THANK YOUOriginally posted by asbirpam
I have a deadline in a week : (
Not much time for QA, huh...
Use stored procedures
CREATE PROC mySproc99
@.key int
AS
SELECT Col_list* FROM myTable99 WHERE Key = myKey99**
GO
EXEC mySproc99 value
GO
* Supply the columns you need from the table
** Supply the name of the key colmn and the value you need to select...
Good luck...
Come back with more specific info if you need help
Showing posts with label available. Show all posts
Showing posts with label available. Show all posts
Wednesday, March 21, 2012
Friday, March 9, 2012
QA vs sproc mystery
Here's a poser (at least to me!)
When I run a select statement in QA the statement uses available indexes
(Index Scan) and is quite fast, but when I take the same script, place it in
a sproc, and execute the sproc, it uses a Table Scan rather than an Index
S
on the Juvenile table - killing performance.
Any pointers would be appreciated
here's the script:
Select
J.JuvenileID,
(J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1)
+ ')' ) as JuvFullName,
Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
From Juvenile J
Inner Join Placement P on J.JuvenileID = P.JuvenileID
Where juvPlaSite = @.SitLocationCode
and juvPlaEndDate IS NULL
and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
@.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
@.JuvenileLNameBeginsWith + '%' END
Order By JuvFullNameWhat column(s) is indexed in Juvenile?|||Can you post the sp?
AMB
"Dazed and Confused" wrote:
> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
> Any pointers would be appreciated
> here's the script:
> Select
> J.JuvenileID,
> (J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1
)
> + ')' ) as JuvFullName,
> Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
> From Juvenile J
> Inner Join Placement P on J.JuvenileID = P.JuvenileID
> Where juvPlaSite = @.SitLocationCode
> and juvPlaEndDate IS NULL
> and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
> @.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
> @.JuvenileLNameBeginsWith + '%' END
> Order By JuvFullName
>|||"Dazed and Confused" <Dazed and Confused@.discussions.microsoft.com> wrote in
message news:C09CDD00-02A2-4199-9A1B-8420771CDF43@.microsoft.com...
> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
Obviate parameter sniffing by assigning parameters to local variables and
using the local variables in the query.
When I run a select statement in QA the statement uses available indexes
(Index Scan) and is quite fast, but when I take the same script, place it in
a sproc, and execute the sproc, it uses a Table Scan rather than an Index
S
on the Juvenile table - killing performance.Any pointers would be appreciated
here's the script:
Select
J.JuvenileID,
(J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1)
+ ')' ) as JuvFullName,
Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
From Juvenile J
Inner Join Placement P on J.JuvenileID = P.JuvenileID
Where juvPlaSite = @.SitLocationCode
and juvPlaEndDate IS NULL
and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
@.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
@.JuvenileLNameBeginsWith + '%' END
Order By JuvFullNameWhat column(s) is indexed in Juvenile?|||Can you post the sp?
AMB
"Dazed and Confused" wrote:
> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S
on the Juvenile table - killing performance.> Any pointers would be appreciated
> here's the script:
> Select
> J.JuvenileID,
> (J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1
)
> + ')' ) as JuvFullName,
> Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
> From Juvenile J
> Inner Join Placement P on J.JuvenileID = P.JuvenileID
> Where juvPlaSite = @.SitLocationCode
> and juvPlaEndDate IS NULL
> and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
> @.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
> @.JuvenileLNameBeginsWith + '%' END
> Order By JuvFullName
>|||"Dazed and Confused" <Dazed and Confused@.discussions.microsoft.com> wrote in
message news:C09CDD00-02A2-4199-9A1B-8420771CDF43@.microsoft.com...
> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S
on the Juvenile table - killing performance.Obviate parameter sniffing by assigning parameters to local variables and
using the local variables in the query.
QA options
Hello,
In QA there are options for displaying results in TEXT, Grid or to a file. It is possible to lock these options so that only one is available. How is it possible to change it back to the default so that any of the 3 can be selected?
Hi there OldtimerMCSE,
There is an option.
From within SSMS go to Tools, Option, Query Results, SQL Server.
There you can select the destination.
Terrence Nevins
SQL Server Program Manager
|||i dont think it is possible to lock/unlock the options
you may need to write your own query analyzer
QA options
Hello,
In QA there are options for displaying results in TEXT, Grid or to a file. It is possible to lock these options so that only one is available. How is it possible to change it back to the default so that any of the 3 can be selected?
Hi there OldtimerMCSE,
There is an option.
From within SSMS go to Tools, Option, Query Results, SQL Server.
There you can select the destination.
Terrence Nevins
SQL Server Program Manager
|||i dont think it is possible to lock/unlock the options
you may need to write your own query analyzer
Saturday, February 25, 2012
q; create index
I need to check if an index available on table T1 and field F1. If not,
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
--
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||=?Utf-8?B?SklNLkgu?= <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:
> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
--
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||=?Utf-8?B?SklNLkgu?= <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:
> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP
q; create index
I need to check if an index available on table T1 and field F1. If not,
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||examnotes <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:
> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||examnotes <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:
> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP
Monday, February 20, 2012
Q: sub-report; show or not show
Hello,
I have a sub-report and I need to show it if the data available otherwise
non of the data should be show including headers and title, how can I do this?
Thanks,Use iif statements on the visibility in properties of each object you want
hidden.
e.g.
=iif(fields!data1.value = 0 or fields!data1.value = "" or fields!data1.value
is system.dbNull,true, false)
says if the data is zero, an empty string or null then don't show it, else
show it.
hope that helps.
Greg
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:35430469-CDF7-4E4F-8E49-3A8576FF429D@.microsoft.com...
> Hello,
> I have a sub-report and I need to show it if the data available otherwise
> non of the data should be show including headers and title, how can I do
> this?
> Thanks,
>|||Hi Greg,
Thanks for your help. Can I do this on the sub-report to show it or hide it
instead of going each item in sub-report and using iif?
Letâ's say I have a parameter, if it is true, show sub-report, if not hide
it. Is this possible?
"Greg" wrote:
> Use iif statements on the visibility in properties of each object you want
> hidden.
> e.g.
> =iif(fields!data1.value = 0 or fields!data1.value = "" or fields!data1.value
> is system.dbNull,true, false)
> says if the data is zero, an empty string or null then don't show it, else
> show it.
> hope that helps.
> Greg
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:35430469-CDF7-4E4F-8E49-3A8576FF429D@.microsoft.com...
> > Hello,
> > I have a sub-report and I need to show it if the data available otherwise
> > non of the data should be show including headers and title, how can I do
> > this?
> > Thanks,
> >
>
>|||Yes that should work fine, right click on the greyed out subreport on your
master report, go to properties and visibility and stick the expression in
the box.
=iif(parameters!name.value = "true",false, true)
if parameter is true then show else don't show.
Greg
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:01060CCD-9332-447D-BC53-E1FB101FE0B8@.microsoft.com...
> Hi Greg,
> Thanks for your help. Can I do this on the sub-report to show it or hide
> it
> instead of going each item in sub-report and using iif?
> Let's say I have a parameter, if it is true, show sub-report, if not hide
> it. Is this possible?
>
> "Greg" wrote:
>> Use iif statements on the visibility in properties of each object you
>> want
>> hidden.
>> e.g.
>> =iif(fields!data1.value = 0 or fields!data1.value = "" or
>> fields!data1.value
>> is system.dbNull,true, false)
>> says if the data is zero, an empty string or null then don't show it,
>> else
>> show it.
>> hope that helps.
>> Greg
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:35430469-CDF7-4E4F-8E49-3A8576FF429D@.microsoft.com...
>> > Hello,
>> > I have a sub-report and I need to show it if the data available
>> > otherwise
>> > non of the data should be show including headers and title, how can I
>> > do
>> > this?
>> > Thanks,
>> >
>>
I have a sub-report and I need to show it if the data available otherwise
non of the data should be show including headers and title, how can I do this?
Thanks,Use iif statements on the visibility in properties of each object you want
hidden.
e.g.
=iif(fields!data1.value = 0 or fields!data1.value = "" or fields!data1.value
is system.dbNull,true, false)
says if the data is zero, an empty string or null then don't show it, else
show it.
hope that helps.
Greg
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:35430469-CDF7-4E4F-8E49-3A8576FF429D@.microsoft.com...
> Hello,
> I have a sub-report and I need to show it if the data available otherwise
> non of the data should be show including headers and title, how can I do
> this?
> Thanks,
>|||Hi Greg,
Thanks for your help. Can I do this on the sub-report to show it or hide it
instead of going each item in sub-report and using iif?
Letâ's say I have a parameter, if it is true, show sub-report, if not hide
it. Is this possible?
"Greg" wrote:
> Use iif statements on the visibility in properties of each object you want
> hidden.
> e.g.
> =iif(fields!data1.value = 0 or fields!data1.value = "" or fields!data1.value
> is system.dbNull,true, false)
> says if the data is zero, an empty string or null then don't show it, else
> show it.
> hope that helps.
> Greg
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:35430469-CDF7-4E4F-8E49-3A8576FF429D@.microsoft.com...
> > Hello,
> > I have a sub-report and I need to show it if the data available otherwise
> > non of the data should be show including headers and title, how can I do
> > this?
> > Thanks,
> >
>
>|||Yes that should work fine, right click on the greyed out subreport on your
master report, go to properties and visibility and stick the expression in
the box.
=iif(parameters!name.value = "true",false, true)
if parameter is true then show else don't show.
Greg
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:01060CCD-9332-447D-BC53-E1FB101FE0B8@.microsoft.com...
> Hi Greg,
> Thanks for your help. Can I do this on the sub-report to show it or hide
> it
> instead of going each item in sub-report and using iif?
> Let's say I have a parameter, if it is true, show sub-report, if not hide
> it. Is this possible?
>
> "Greg" wrote:
>> Use iif statements on the visibility in properties of each object you
>> want
>> hidden.
>> e.g.
>> =iif(fields!data1.value = 0 or fields!data1.value = "" or
>> fields!data1.value
>> is system.dbNull,true, false)
>> says if the data is zero, an empty string or null then don't show it,
>> else
>> show it.
>> hope that helps.
>> Greg
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:35430469-CDF7-4E4F-8E49-3A8576FF429D@.microsoft.com...
>> > Hello,
>> > I have a sub-report and I need to show it if the data available
>> > otherwise
>> > non of the data should be show including headers and title, how can I
>> > do
>> > this?
>> > Thanks,
>> >
>>
Subscribe to:
Posts (Atom)