Determining Security: efficient

Showing posts with label efficient. Show all posts

Sunday, March 25, 2012

Determine if more than one row returned

I have an ASP/ADO application querying an SQL Server DB. I want know the
most efficient way to determine if more than one row is returned from a
query. If more than one row is returned, the user will be presented with a
choice of which row to process. If only one row is returned, I want to skip
this stage, and process that single row immediately.
I can think of a number of ways of acheiving this (eg. .recordcount) but I'm
looking for the slickest and most efficient method.
Any thoughts or suggestions
Thanks
CJMCJM wrote:
> I have an ASP/ADO application querying an SQL Server DB. I want know
> the most efficient way to determine if more than one row is returned
> from a query. If more than one row is returned, the user will be
> presented with a choice of which row to process. If only one row is
> returned, I want to skip this stage, and process that single row
> immediately.
> I can think of a number of ways of acheiving this (eg. .recordcount)
> but I'm looking for the slickest and most efficient method.
> Any thoughts or suggestions
>
Best option: Use a stored procedure that only returns the data if more
than one row meeting the criteria exist.
Second best:
Use GetRows to put the recordset data into an array and check its upper
index bound using the ubound function.
This has the added benefit of allowing you to:
1. use the efficient server-side forward-only cursor (which does not
support recordcount)
2. immediately close and destroy the recordset
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi,
Thanks for your post!
My understanding of your issue is:
You wanted the most efficient way to retrieve the result row count.
If I have misunderstood, please let me know.
I recommend you write a common stored procedure retrieving the row count of
any query result.
Here is a sample just for reference:
create procedure proc_getqueryrowscount
(
@.strquery varchar(1000)
)
as
declare @.strcountquery varchar(1000)
select @.strcountquery = 'select count(*) from ( ' + @.strquery + ' ) v '
exec ( @.strcountquery )
In your application, design and implement a common application interface
function, such as:
int GetDBQueryCount(String strQuery)
In any place when you need to get a query result row count, it's convenient
to use this function.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w

to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Bob,
> Thanks for the response...
> Option 2 is the best in this case... there may only be one row
> returned (which is fine) but in this case I need to handle it
> differently...
>
Why not handle it in the stored procedure?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response...
> Option 2 is the best in this case... there may only be one row
> returned (which is fine) but in this case I need to handle it
> differently...
>
Just to expand, something like this:
create procedure ...
declare @.rows int
set @.rows=(select count(*) from table where <criteria> )
if @.rows=0
do something
return
if @.rows = 1
do something else
return
if @.rows > 1
select <columns> from table where <criteria>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OhFw993mGHA.2264@.TK2MSFTNGP04.phx.gbl...
> Just to expand, something like this:
> create procedure ...
> declare @.rows int
> set @.rows=(select count(*) from table where <criteria> )
> if @.rows=0
> do something
> return
> if @.rows = 1
> do something else
> return
> if @.rows > 1
> select <columns> from table where <criteria>
>
It's client-side stuff...need user interaction...
If no rows returned, display error message
If 1 row returned, immediately go to editing page for that row...
If several rows returned, display list. User picks row and it redirects to
editing page...
CJM|||Hi CJM,
If there are at most 2 values, then most methods will probably be
equally fast. However, if there are cases where there are much more than
2 values, then the solution below is probably the fastest. This is
because query execution will end when the second value is found.
Note however, that it is based on the proprietary behavior of SQL Server
that does not require just one row/value to be assigned to a local
variable.
Declare @.the_value int
SELECT TOP 2 @.the_value = value
FROM MyTable
WHERE some_column = 'some value'
If @.@.rowcount > 1
Begin
print 'Please select a value'
..
End
Else
SELECT @.the_value AS "This is the value"
HTH,
Gert-Jan
CJM wrote:
> I have an ASP/ADO application querying an SQL Server DB. I want know the
> most efficient way to determine if more than one row is returned from a
> query. If more than one row is returned, the user will be presented with a
> choice of which row to process. If only one row is returned, I want to ski
p
> this stage, and process that single row immediately.
> I can think of a number of ways of acheiving this (eg. .recordcount) but I
'm
> looking for the slickest and most efficient method.
> Any thoughts or suggestions
> Thanks
> CJM

Wednesday, March 21, 2012

detect existence of datetime fields

I'm looking for an efficient t-sql script to loop through all user
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)

Help greatly appreciated.."JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401022017.34730d8@.posting.google.co m...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..

Here is a script that does a reasonable task of generating
a useful data dictionary out of any database.

To identify the datetime fields simply add
a restriction line in the WHERE bit ... such as
and t.name in ('DateTime', 'SmallDateTime')

select substring(o.name,1,50) as "Table Name",
o.type "Typ",
c.colid,
substring(c.name,1,30) as "Column Name",
substring(t.name,1,30) as "DataType",
c.length

from sysobjects o
left join syscolumns c on (o.id=c.id)
left join systypes t on (c.xusertype=t.xusertype)
where o.type = 'U'
order by 1,3|||jonahturnedwhaler@.yahoo.com (JTWhaler) wrote in message news:<ed564348.0401022017.34730d8@.posting.google.com>...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..

Hi Jonah

The attached T-sql will create a database table that contains an entry
for each datetime column in the database

drop table datetime_column

create table datetime_column
(
table_name varchar(200)
,column_name varchar(200)
,datetime_value datetime null
)
declare @.table_schema as varchar(200)
declare @.table_name as varchar(200)
declare @.column_name as varchar(200)
declare @.query as nvarchar(1000)

DECLARE column_cursor CURSOR FOR
SELECT
table_name, column_name
FROM
INFORMATION_Schema.columns
where
data_type = 'datetime'
and table_name != 'datetime_column'

open column_cursor

FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name

while @.@.fetch_status = 0
begin
select @.query = ' insert into datetime_column select ' + '''' +
@.table_name + '''' + ',' + '''' + @.column_name + '''' + ',' +
@.column_name + ' from ' + @.table_name
--select @.query
exec sp_executesql @.query

FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name
end

close column_cursor
deallocate column_cursor
select * from datetime_column

Friday, February 24, 2012

Design Question for Large Table

Hi,

What's the most efficient way to store the following information:

* Table contains 1 million listings
* Each listing can be geo-targeted to any of the 200+ countries
* Searches return listings based on geo-location

Storage options:

Option #1 (normalized)
* ListingsTable (PK listingID int) [1 million rows]
* ListingGeoLocations (listingID, geoLocationID) [could be up to 200 million rows]

Option #2 (denormalized)
* ListingsTable (PK listingID int, binary(32) with bit-mask consisting of 200 bits one for each location)

Did anyone have experience with similar structures? Which option is more efficient?

Thanks,
Avoption 2 sounds inferior to me

let's say you were looking for listings corresponding to the 37th bit in the bitmask, how would you find them?

by inspecting the bitmask of all 1 million rows, that's how|||I'm too tired to answer

Look up normalization on google|||Option #1.

As Rudy pointed out, you are going to have problems doing bitwise calculations on bitmasks over 30 bits, after which you exceed the capacity of the BigInt datatype.

Also, while under scenario #1 your table COULD reach 200 million rows, in reality what is the average number of countries you expect for each geolocation? Probably significantly less than 200.|||I understand that option #1 is normalized and will allow the search for listings in a specific country using query. However, say when a search is performed, it is based on an set of keywords (which are also tied to listings).

So say you looking for "dentists". You can about 100 listings that are related. Now, the APPLICATION (not SQL) will simply "filter" the 100 listings by doing geo-specific bitwise comparissons and will only display the listings that have the proper country bit set.

Still Option #1? The search needs to be very fast.|||You are NOT going to do bitwise operations on a 200 value bitmask.|||So say you looking for "dentists". You can about 100 listings that are related. you can? you can what? how does that work?|||you can? you can what? how does that work?

Ask Harry potter|||Why not have a
Listings table (Pk ListingID)
Locations Table (PK LocationID)
and,
ListingLocations table (PK ListingID, LocationID)

ListingLocations is an intersection table, used to define a many-to-many relationship. The two required fields, ListingID and LocationID are also foreign keys to the appropriate table.

Sunday, March 25, 2012

Determine if more than one row returned

Wednesday, March 21, 2012

detect existence of datetime fields

Friday, February 24, 2012

Design Question for Large Table

Determining Security

Blog Archive

About Me