A view based on a parameter

Hello.

After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.

One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.

So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.

Any help would be appreciated!

Shirley

It sounds like you might want a stored procedure or user-defined function. Both of those allow you to pass parameters, both in and out. I found a really good reference to an Access-to-SQL Server information site here:

http://www.informit.com/discussion/index.asp?postid=6a35b938-8029-40a8-9511-95bd1b7f1255&rl=1

Buck Woody

A view based on a parameter

Hello.

After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.

One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.

So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.

Any help would be appreciated!

Shirley

It sounds like you might want a stored procedure or user-defined function. Both of those allow you to pass parameters, both in and out. I found a really good reference to an Access-to-SQL Server information site here:

http://www.informit.com/discussion/index.asp?postid=6a35b938-8029-40a8-9511-95bd1b7f1255&rl=1

Buck Woody

a question or two re fulltext queries

hi,
i'm using SQL Server 2000 (SP3, I think).
i am looking at SQL for finding documents (table DOC) based on a couple of
criteria, one of which involves a fulltext index on a related table (TXT).
i discovered today that a less selective CONTAINS() clause:
CONTAINS (txt_stripped, 'daimler')
results in disastrous performance when I ask for the "top 10" documents
sorted by published date (a field of DOC) descending.
if I
1) include a more selective contains clause:
CONTAINS (txt_stripped, 'daimler AND mercedes')
2) omit the "top 10" (there are only125 documents total.) or
3) omit the "ORDER BY doc_pubfrom DESC" clause
the query performs fine.
i examined the query plans and discovered that in the bad case the first
thing done is to walk the doc_pubfrom index (figures, I guess: top 10 order
by...). in the other cases the first thing done is the "remote scan" of the
fulltext index, which is what I want done first.
i already know how I'll solve this problem: i don't need the "top 10" at
this point -- it was just a whim while prototyping.
my questions, though for future reference:
1) is there syntax to influence SQL's query planning specifically to prefer
the full text index ?
2) is there a way to get the query plan textually? (the graphic view is
great but I wondered what I would do if I wanted to mail it or post it)
cheers,
Tim Hanson
1) Could you post the entire schema with indexes?
2) use set showplan_text on
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:uTlE0P$KGHA.1180@.TK2MSFTNGP09.phx.gbl...
> hi,
> i'm using SQL Server 2000 (SP3, I think).
> i am looking at SQL for finding documents (table DOC) based on a couple of
> criteria, one of which involves a fulltext index on a related table (TXT).
> i discovered today that a less selective CONTAINS() clause:
> CONTAINS (txt_stripped, 'daimler')
> results in disastrous performance when I ask for the "top 10" documents
> sorted by published date (a field of DOC) descending.
> if I
> 1) include a more selective contains clause:
> CONTAINS (txt_stripped, 'daimler AND mercedes')
> 2) omit the "top 10" (there are only125 documents total.) or
> 3) omit the "ORDER BY doc_pubfrom DESC" clause
> the query performs fine.
> i examined the query plans and discovered that in the bad case the first
> thing done is to walk the doc_pubfrom index (figures, I guess: top 10
> order by...). in the other cases the first thing done is the "remote scan"
> of the fulltext index, which is what I want done first.
> i already know how I'll solve this problem: i don't need the "top 10" at
> this point -- it was just a whim while prototyping.
> my questions, though for future reference:
> 1) is there syntax to influence SQL's query planning specifically to
> prefer the full text index ?
> 2) is there a way to get the query plan textually? (the graphic view is
> great but I wondered what I would do if I wanted to mail it or post it)
> cheers,
> Tim Hanson
>
|||thanks, Hillary, that was quick!
re 1) by "schema" you mean all affected tables? that would be a serious
handful. i'd hate to trouble you and post so much stuff. do you have any
general hints on keywords for nudging the query plan in favor of fulltext
index first?
re 2) thanks!!
cheers,
Tim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OPg4yc$KGHA.668@.TK2MSFTNGP11.phx.gbl...
> 1) Could you post the entire schema with indexes?
> 2) use set showplan_text on
> --
> Hilary Cotter
|||Just the problem tables. I don't have any hints off the top of my head. You
might be able to do a force order query hint which might help.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:uyE2Ow$KGHA.4052@.TK2MSFTNGP15.phx.gbl...
> thanks, Hillary, that was quick!
> re 1) by "schema" you mean all affected tables? that would be a serious
> handful. i'd hate to trouble you and post so much stuff. do you have any
> general hints on keywords for nudging the query plan in favor of fulltext
> index first?
> re 2) thanks!!
> cheers,
> Tim
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OPg4yc$KGHA.668@.TK2MSFTNGP11.phx.gbl...
>
>
|||I have the same problem, when I search for a (litle) word that apears
in many records, the query that uses full-text index takes to long to
execute.
I use Top 1000, Contains() and Order By.
How can I optimize this without changing the TOP and Order by?
Thaks
|||Can you use a containstable and the top_n_by_rank clause? IE
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
, 10
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<paulo.gonc@.gmail.com> wrote in message
news:1139421012.509817.170610@.g43g2000cwa.googlegr oups.com...
>I have the same problem, when I search for a (litle) word that apears
> in many records, the query that uses full-text index takes to long to
> execute.
> I use Top 1000, Contains() and Order By.
> How can I optimize this without changing the TOP and Order by?
> Thaks
>

A question about SQL queries

Ok, I'm sorry if this has already been answered. I didn't know what to look for (or I would've solved my problem on my own)

So I have two tables, one called"users" and the other one called"posts". I'm retrieving everything from the posts-table and the userid and username from the users-table. The thing I can't get working is that I ONLY want toretrieve the username for thespecific userid that made the post. I'm sorry for the lack of explanation and the lack of code (since I actually don't know what to do I don't have any code) and the fact that english isn't my native language.

Thanks in advance, if there's anyone that understands me

standard SQL statement for this problem would be:

assume table user has following columns : userid,username table post has following column : userid, postreply

select a.userid,a.username,b.postreply

from post b,user a

where b.userid=a.userid

now you have to adapt the statement for you flavor of database.

if you use the sql query editor to attach to your database type, it should generate the correct style for your command

to ensure that you get all records from one table even if there is no matching record in the other table then you start playing with inner joins and outter joins.

|||

Fair enough :D I will try that next week, going away for the weekend but I will get back to you if this worked out for me :)

EDIT: Just wanted to say I worked out fine for me :)

"SELECT users.userid, users.username, posts.postid, posts.userid, posts.message FROM users, posts WHERE users.userid = posts.userid"