bcp, queryout and pipe delimiter

When I execute the following string within a stored procedure:
(The string has been modified to mask company info)

bcp "Exec dbname.dbo.GetSalesCodesContracts" queryout d:\ftp\sfa\SalescodeContract-1.txt -m1 -c -t| -Uxxxxx -Pxxxxx -Sxxxxx

I get the following error: The name specified is not recognized as an
internal or external command, operable program or batch file.

But if I change -t| to -t, (comma) it works ok and produces a comma delimited file.

The pipe character seems to be the problem. The help file on delimiters says you can use the pipe.

This is running under SQL Server 7.0.

Any help would be appreciated.

RichardAre you using bulk insert or the bcp command line utility?

'|' is used for command line re-direction, most commonly to more, find and sort. It can also be used to re-direct the output of one application to another application. I suspect the command interpriter sees the '|' and tries to re-direct the result of your bcp command to the '-UXXXXX' application which as you have found out does not exist.

Hope this helped.|||Try using -t'|'|||Try using -t'|'

This did not work for me. Instead, I finally discovered, -t^| did the trick. (The DOS shell interpreter uses the ^ character to escape the | and interpret the whole thing as a literal to be passed to bcp, rather than as part of the DOS commandline itself.)|||I would do this with t-sql and xp_cmdshellsql

Basic SQL server setup questions...

I'm new to using SQL server and have a few setup questions.
I'm setting up a database for the company I work for. Should I setup a different database for each division in our company (e.g. accounting, project management, estimating, etc.), or should I use just one database? It seems like it would be easier to use one database so I only have to use one connection, but is this ideal? I want all the info from the different divisions to be used together, but at this point I'm confused as to how to connect different databases together, if I were to go this way.

Thanks for any input or ideas!I create seperate databases based on the app function - not the company division.
many times you'll get people from different functional areas all using the same apps...

anyways, you can get to all the data in a single db with a single connection if its all on the same server. you just need to qualify the table names in your queries with a prefix of the database name.

i.e.

AccountingDB.dbo.table.column

since this is a pain to type, in one of our dbs that shared a user table with an existing db, we created a view that pulled all users from the other db. now our new database has a local view that we can treat like a user table...one connection can pull data from 2 (or more) databases :-)

Basic security setup

Hi there
Our company has just started using RS and I would like to
know how to set up basic security.
Our setup:
We have RS installed on one of our SQL servers. Our
Analysis department creates reports on their PC's (with
Reporing Services & Visual Studio.net installed on their
local PC). They will then deploy the reports to our RS
server and the Marketing department will access reports
through the RS web interface.
What I am struggling with:
I cannot figure out how to let users see reports without
giving them adminitrator rights on the RS server. I have
tried giving them access to the reportserver database -
this gives them access to the reportserver webpage, but
they cannot see any reports (I gave them access to
specific reports in RS manager).
I'm sure it's something stupid that I'm not doing, but
cannot figure out what it is. Could someone please help
me.
Thanks,
TheresaIf you are a 'Content Manager' on the report server (by default all local
admins are content managers) you can set permission on RS objects. Go to
the object in question, on the property tab there is a security sub tab
which will allow you to give users access rights. You most likely want to
use the Browser role but you will have to investigate to see what fits your
needs.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Theresa dP" <theresa@.discussions.microsoft.com> wrote in message
news:43b401c49035$39843a90$a301280a@.phx.gbl...
> Hi there
> Our company has just started using RS and I would like to
> know how to set up basic security.
> Our setup:
> We have RS installed on one of our SQL servers. Our
> Analysis department creates reports on their PC's (with
> Reporing Services & Visual Studio.net installed on their
> local PC). They will then deploy the reports to our RS
> server and the Marketing department will access reports
> through the RS web interface.
> What I am struggling with:
> I cannot figure out how to let users see reports without
> giving them adminitrator rights on the RS server. I have
> tried giving them access to the reportserver database -
> this gives them access to the reportserver webpage, but
> they cannot see any reports (I gave them access to
> specific reports in RS manager).
> I'm sure it's something stupid that I'm not doing, but
> cannot figure out what it is. Could someone please help
> me.
> Thanks,
> Theresa

Basic Security Question for remote app

I'm designing a C# winforms app that will be used by 500 customers/companies. The customers will use the app to edit data for their company on the remote SQL Server. What's the best way to manage security in terms of logins/passwords? Should each user have a different login? (probably not) Or should the application have it's own login/password that is encrypted and the user never knows what the login/password is?

Also, is it necessary to use Web Services for this application? Any other thoughts/comments are very welcome.

If there are 500 DIFFERENT companies, would you want a user for companyA to be able to change information for CompanyB?

If not, then using a common login/permission set is NOT a good idea and individual logins seem necessary. It seems like the question then becomes "How to mangage a large number of logins/permissions?"

Perhaps more details are would be useful to fully understand your scenario...

|||Here's some more detail:

Company Table
CompanyId
CompanyName

ProductTable
ProductId
CompanyId
ProductName
Price

Then, we ship out applications to all the different companies.
The application should know what their CompanyId is. In the C# application, there will be a grid that displays all of the Products for their CompanyId and allows the user to update those products (for example, change the Price for a given product).

At this point, I'm just kind of at a loss for the best way to design this in terms of SQL Server Security and logins/passwords, etc. Hope this is not too vague.
|||

This will prove to be an interesting challenge. I can already imagine some interprising soul for a company 'exploring' the products for other companies -and who knows, even changing prices...

The 'safest' way to control data access (keeping each user to his/her own company) is by knowing what user is attempting data access on each query execution. Without a company specific login (or user specific login), you will have a difficult (perhaps even impossible) time keeping users out of each others data.

Yes, on the surface, the application can appear to take care of it. But are you willing to 'guarantee' all companies that their data is safe from prying eyes and safe from accidental or intentional alteration.

The database 'should' know, with certainity, who (user or company) is accessing data. I think that you have no option but to assign individual logins to each company/user (all users for a company 'could' use the same login).

You will need a process to manage Logins, adding users, resetting lost passwords, disabling or removing users. And a way to map a login to a CompanyID. Once someone logins in, the CompanyID value is supplied to be used in WHERE criteria. I wouldn't accept a CompanyID from the application.

Personally, from the data professional perspective, I wouldn't trust my career to expecting the application to control all access.

Basic Questions about SQL Server IO Optimization

Hi,
I am a programmer and my understanding of low-level database I/O
issues is limited. I've just taken a job with a company that is
currently using classic asp with inline sql. I come from an
environment with stored procs and I'm used to dealing in full records
and recordsets. We've been discussing database IO and my colleagues
here feel that it's necessary with classic asp to only pull in fields
you need, even if you're working with one record in a table. This,
they feel, will will cut down on IO time and bandwidth.
In my reading, I've gotten the impression that the days of worrying
about how many fields you bring in from one record are in the past
since I/O involves paging (meaning that more than a couple of fields
are brought in no matter what) and disks are so fast it doesn't
matter.
To support their arguments they've brought up the issue of bringing
in ntext fields which can slow down an I/0 operation. In this case I
agree with them but there methodology goes against the whole idea of
standard I/O concepts (e.g. creating stored procs to read and write to
at least an entire record).
I'm not certain of my take on things. I hope that someone might give
me some guidance and perhaps some articles to read on this subject.
What we have here is a lot of disparate in-line sql which I feel is
redudant and not easily modified since it's all over the place (i.e.
the addtion of one critical field in a table would require changing
hundreds of isolated inline sql strings).
Any advice would be appreciated.
Thanks,
Fig
"fig000" <neilnewton001@.yahoo.com> wrote in message
news:1176731759.224287.121120@.y5g2000hsa.googlegro ups.com...
> Hi,
> I am a programmer and my understanding of low-level database I/O
> issues is limited. I've just taken a job with a company that is
> currently using classic asp with inline sql. I come from an
> environment with stored procs and I'm used to dealing in full records
> and recordsets. We've been discussing database IO and my colleagues
> here feel that it's necessary with classic asp to only pull in fields
> you need, even if you're working with one record in a table. This,
> they feel, will will cut down on IO time and bandwidth.
Generally they're probably correct. If you're suggesting a select * is "ok"
your own example below seems to agree with the problems that might cause.
Also, consider a case where their select statement only brings back 2 fields
from a row, but those 2 fields are in a covering index. In that case, the
IO can be VERY effecient.
If they were bring back additional rows not in the covered index and not
using them, the IO usage can go way up.
Overall though I'd agree, getting rid of inline stuff and replacing with
stored procedures would probably be even better. :-)

> In my reading, I've gotten the impression that the days of worrying
> about how many fields you bring in from one record are in the past
> since I/O involves paging (meaning that more than a couple of fields
> are brought in no matter what) and disks are so fast it doesn't
> matter.
> To support their arguments they've brought up the issue of bringing
> in ntext fields which can slow down an I/0 operation. In this case I
> agree with them but there methodology goes against the whole idea of
> standard I/O concepts (e.g. creating stored procs to read and write to
> at least an entire record).
> I'm not certain of my take on things. I hope that someone might give
> me some guidance and perhaps some articles to read on this subject.
> What we have here is a lot of disparate in-line sql which I feel is
> redudant and not easily modified since it's all over the place (i.e.
> the addtion of one critical field in a table would require changing
> hundreds of isolated inline sql strings).
> Any advice would be appreciated.
> Thanks,
> Fig
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Greg,
Thanks for answering. I can see that there is no simple answer to
this question. What I wonder then, is how the whole current trend got
started: creating a stored procedure for each of the four crud
operations for each table in a database. This is sort of underscored
by the development of all the wizards (datadapter in 2003 and
tableadapter in 2005). Are these simply bad practices which simplify
programming?
I guess I can see these issues if you are reading a large number of
records (which would make the use of the ado.net recordsets dangerious
if not used carefully). But for one record (or a few) wouldn't the
difference between reading two fields or 50 be minimal in terms of
database I/O? If I'm right, wouldn't the use of a standardized "select
*" type of functionality be more convenient from a coding standpoint
and cause little trouble in terms of database I/O?
I guess my final question would be: how could possibly put hundreds
of disparate queries selecting various sets of fields from a table or
tables, into stored procs? Wouldn't the complexity of that be an
argument for standardizing the SQL for a table into four simple crud
queries that could be called from any part of the system. Wouldn't
that also make modifying the queries (say for adding a new crictical
field) much easier.
I admit I don't know much about database internals so please forgive
my possible ignorance.
Thanks again for answering this sort of off the wall request. Are
there any articles or books that might clear this up for me?
Fig
On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> "fig000" <neilnewton...@.yahoo.com> wrote in message
> news:1176731759.224287.121120@.y5g2000hsa.googlegro ups.com...
>
> Generally they're probably correct. If you're suggesting a select * is "ok"
> your own example below seems to agree with the problems that might cause.
> Also, consider a case where their select statement only brings back 2 fields
> from a row, but those 2 fields are in a covering index. In that case, the
> IO can be VERY effecient.
> If they were bring back additional rows not in the covered index and not
> using them, the IO usage can go way up.
> Overall though I'd agree, getting rid of inline stuff and replacing with
> stored procedures would probably be even better. :-)
>
>
>
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Fig,
SQL Server uses a cost based optimizer that is getting smarter with each
release. The more to-the-point your query gets, the more options the
optimizer has to achieve this as fast and efficient as possible. If you
don't need all columns of a row, then don't ask for it.
There are many examples where the optimizer can speed things up if you
ask less, so the ones below are just some examples.
- If there is a covering index (an index that holds all the columns
needed for your query) that it can be used instead of the base table. A
covering index is typically much smaller than the table, so on average
less I/O is required to retrieve the same number of rows
- If a row's size expands to over 4096 bytes, for example because you
just update a varchar column with a bigger string, then the data of this
column will be moved to a separate page. If you do not use this column
in your select query, this extra page does not have to be retrieved
- If you have text, ntext or image column in your table. Their values
are typically (but not always) stored in seperate pages
- If there is an indexed view defined for the table, it might be used
instead of the actual table (provided you are using Enterprise Edition)
One last thing. You are right, that if the storage engine has to
retrieve a page of table data, then (apart from the cases above) most or
all row data is retrieved. However, if the query plan has multiple
operators, all columns that are selected have to be kept in memory and
(possible) processed. This might involve I/O in tempdb. Also, the data
has to be piped to your application. Over a WAN that might cause
(serious) delays.
HTH,
Gert-Jan
fig000 wrote:
> Hi,
> I am a programmer and my understanding of low-level database I/O
> issues is limited. I've just taken a job with a company that is
> currently using classic asp with inline sql. I come from an
> environment with stored procs and I'm used to dealing in full records
> and recordsets. We've been discussing database IO and my colleagues
> here feel that it's necessary with classic asp to only pull in fields
> you need, even if you're working with one record in a table. This,
> they feel, will will cut down on IO time and bandwidth.
> In my reading, I've gotten the impression that the days of worrying
> about how many fields you bring in from one record are in the past
> since I/O involves paging (meaning that more than a couple of fields
> are brought in no matter what) and disks are so fast it doesn't
> matter.
> To support their arguments they've brought up the issue of bringing
> in ntext fields which can slow down an I/0 operation. In this case I
> agree with them but there methodology goes against the whole idea of
> standard I/O concepts (e.g. creating stored procs to read and write to
> at least an entire record).
> I'm not certain of my take on things. I hope that someone might give
> me some guidance and perhaps some articles to read on this subject.
> What we have here is a lot of disparate in-line sql which I feel is
> redudant and not easily modified since it's all over the place (i.e.
> the addtion of one critical field in a table would require changing
> hundreds of isolated inline sql strings).
> Any advice would be appreciated.
> Thanks,
> Fig
|||Best practice for me is usually what you said, and what they said.
Return only what you need, and use stored procedures.
On Apr 16, 3:51 pm, "fig000" <neilnewton...@.yahoo.com> wrote:
> Greg,
> Thanks for answering. I can see that there is no simple answer to
> this question. What I wonder then, is how the whole current trend got
> started: creating a stored procedure for each of the four crud
> operations for each table in a database. This is sort of underscored
> by the development of all the wizards (datadapter in 2003 and
> tableadapter in 2005). Are these simply bad practices which simplify
> programming?
> I guess I can see these issues if you are reading a large number of
> records (which would make the use of the ado.net recordsets dangerious
> if not used carefully). But for one record (or a few) wouldn't the
> difference between reading two fields or 50 be minimal in terms of
> database I/O? If I'm right, wouldn't the use of a standardized "select
> *" type of functionality be more convenient from a coding standpoint
> and cause little trouble in terms of database I/O?
> I guess my final question would be: how could possibly put hundreds
> of disparate queries selecting various sets of fields from a table or
> tables, into stored procs? Wouldn't the complexity of that be an
> argument for standardizing the SQL for a table into four simple crud
> queries that could be called from any part of the system. Wouldn't
> that also make modifying the queries (say for adding a new crictical
> field) much easier.
> I admit I don't know much about database internals so please forgive
> my possible ignorance.
> Thanks again for answering this sort of off the wall request. Are
> there any articles or books that might clear this up for me?
> Fig
> On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
>
> <mooregr_deletet...@.greenms.com> wrote:
>
>
>
>
>
>
>
> - Show quoted text -
|||"fig000" <neilnewton001@.yahoo.com> wrote in message
news:1176753103.971027.241230@.y80g2000hsf.googlegr oups.com...
> Greg,
> Thanks for answering. I can see that there is no simple answer to
> this question. What I wonder then, is how the whole current trend got
> started: creating a stored procedure for each of the four crud
> operations for each table in a database. This is sort of underscored
> by the development of all the wizards (datadapter in 2003 and
> tableadapter in 2005). Are these simply bad practices which simplify
> programming?
Not really sure what you're asking here.

> I guess I can see these issues if you are reading a large number of
> records (which would make the use of the ado.net recordsets dangerious
> if not used carefully). But for one record (or a few) wouldn't the
> difference between reading two fields or 50 be minimal in terms of
> database I/O?
Not necessarily. Let's say you want to read one record.
In that case, (I'm going to assume basic, row, no large blobs), you need to
read one 8K page.
Fair enough. Even if you only need two fields (say a total of 100 bytes)
you're reading an 8K page.
However, consider the situation where each call only returns 1 row, but you
call that code 100 times.
Worse case scenario, you need to read 100 8K pages. (This assumes a select
* from...)
Best case with say a covering index and returning 100 bytes is reading in 2
8K pages.
That's 50 times more effecient.
Now, in the worst case, if the pages are scattered all over the disk, you
might need a physical read for each one. That's 100 physical reads.
In the best case, you need two physical reads and in some cases just ONE
physical read (since the engine will generally read multiple pages in a row
in one pass). Combine that with lack of arm movement and this suddenly
becomes far more effecient.

> If I'm right, wouldn't the use of a standardized "select
> *" type of functionality be more convenient from a coding standpoint
> and cause little trouble in terms of database I/O?
Well if you want a worse case of 100x slower I/O sure that's a little
trouble. :-)
(and note we're assuming you don't have any large blobs returned in a select
* that are thrown away.)
And actually a select * could be far worse than the 100 reads above in some
cases, depending on choices the optimizer will make.

> I guess my final question would be: how could possibly put hundreds
> of disparate queries selecting various sets of fields from a table or
> tables, into stored procs? Wouldn't the complexity of that be an
> argument for standardizing the SQL for a table into four simple crud
> queries that could be called from any part of the system. Wouldn't
> that also make modifying the queries (say for adding a new crictical
> field) much easier.
Well from the above, sounds more like the whole application might need to be
reviewed.
You can get other benefits from stored procs also, query/plan re-use,
protect against SQL injection attacks.

> I admit I don't know much about database internals so please forgive
> my possible ignorance.
> Thanks again for answering this sort of off the wall request. Are
> there any articles or books that might clear this up for me?
Yeah.. Inside SQL Server either for 2000 or 2005 depending on what you're
using.

> Fig
> On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Basic Questions about SQL Server IO Optimization

Hi,
I am a programmer and my understanding of low-level database I/O
issues is limited. I've just taken a job with a company that is
currently using classic asp with inline sql. I come from an
environment with stored procs and I'm used to dealing in full records
and recordsets. We've been discussing database IO and my colleagues
here feel that it's necessary with classic asp to only pull in fields
you need, even if you're working with one record in a table. This,
they feel, will will cut down on IO time and bandwidth.
In my reading, I've gotten the impression that the days of worrying
about how many fields you bring in from one record are in the past
since I/O involves paging (meaning that more than a couple of fields
are brought in no matter what) and disks are so fast it doesn't
matter.
To support their arguments they've brought up the issue of bringing
in ntext fields which can slow down an I/0 operation. In this case I
agree with them but there methodology goes against the whole idea of
standard I/O concepts (e.g. creating stored procs to read and write to
at least an entire record).
I'm not certain of my take on things. I hope that someone might give
me some guidance and perhaps some articles to read on this subject.
What we have here is a lot of disparate in-line sql which I feel is
redudant and not easily modified since it's all over the place (i.e.
the addtion of one critical field in a table would require changing
hundreds of isolated inline sql strings).
Any advice would be appreciated.
Thanks,
Fig"fig000" <neilnewton001@.yahoo.com> wrote in message
news:1176731759.224287.121120@.y5g2000hsa.googlegroups.com...
> Hi,
> I am a programmer and my understanding of low-level database I/O
> issues is limited. I've just taken a job with a company that is
> currently using classic asp with inline sql. I come from an
> environment with stored procs and I'm used to dealing in full records
> and recordsets. We've been discussing database IO and my colleagues
> here feel that it's necessary with classic asp to only pull in fields
> you need, even if you're working with one record in a table. This,
> they feel, will will cut down on IO time and bandwidth.
Generally they're probably correct. If you're suggesting a select * is "ok"
your own example below seems to agree with the problems that might cause.
Also, consider a case where their select statement only brings back 2 fields
from a row, but those 2 fields are in a covering index. In that case, the
IO can be VERY effecient.
If they were bring back additional rows not in the covered index and not
using them, the IO usage can go way up.
Overall though I'd agree, getting rid of inline stuff and replacing with
stored procedures would probably be even better. :-)

> In my reading, I've gotten the impression that the days of worrying
> about how many fields you bring in from one record are in the past
> since I/O involves paging (meaning that more than a couple of fields
> are brought in no matter what) and disks are so fast it doesn't
> matter.
> To support their arguments they've brought up the issue of bringing
> in ntext fields which can slow down an I/0 operation. In this case I
> agree with them but there methodology goes against the whole idea of
> standard I/O concepts (e.g. creating stored procs to read and write to
> at least an entire record).
> I'm not certain of my take on things. I hope that someone might give
> me some guidance and perhaps some articles to read on this subject.
> What we have here is a lot of disparate in-line sql which I feel is
> redudant and not easily modified since it's all over the place (i.e.
> the addtion of one critical field in a table would require changing
> hundreds of isolated inline sql strings).
> Any advice would be appreciated.
> Thanks,
> Fig
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Greg,
Thanks for answering. I can see that there is no simple answer to
this question. What I wonder then, is how the whole current trend got
started: creating a stored procedure for each of the four crud
operations for each table in a database. This is sort of underscored
by the development of all the wizards (datadapter in 2003 and
tableadapter in 2005). Are these simply bad practices which simplify
programming?
I guess I can see these issues if you are reading a large number of
records (which would make the use of the ado.net recordsets dangerious
if not used carefully). But for one record (or a few) wouldn't the
difference between reading two fields or 50 be minimal in terms of
database I/O? If I'm right, wouldn't the use of a standardized "select
*" type of functionality be more convenient from a coding standpoint
and cause little trouble in terms of database I/O?
I guess my final question would be: how could possibly put hundreds
of disparate queries selecting various sets of fields from a table or
tables, into stored procs? Wouldn't the complexity of that be an
argument for standardizing the SQL for a table into four simple crud
queries that could be called from any part of the system. Wouldn't
that also make modifying the queries (say for adding a new crictical
field) much easier.
I admit I don't know much about database internals so please forgive
my possible ignorance.
Thanks again for answering this sort of off the wall request. Are
there any articles or books that might clear this up for me?
Fig
On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:[vbcol=seagreen]
> "fig000" <neilnewton...@.yahoo.com> wrote in message
> news:1176731759.224287.121120@.y5g2000hsa.googlegroups.com...
>
>
> Generally they're probably correct. If you're suggesting a select * is "o
k"
> your own example below seems to agree with the problems that might cause.
> Also, consider a case where their select statement only brings back 2 fiel
ds
> from a row, but those 2 fields are in a covering index. In that case, the
> IO can be VERY effecient.
> If they were bring back additional rows not in the covered index and not
> using them, the IO usage can go way up.
> Overall though I'd agree, getting rid of inline stuff and replacing with
> stored procedures would probably be even better. :-)
>
>
>
>
>
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html[/vbco
l]|||Fig,
SQL Server uses a cost based optimizer that is getting smarter with each
release. The more to-the-point your query gets, the more options the
optimizer has to achieve this as fast and efficient as possible. If you
don't need all columns of a row, then don't ask for it.
There are many examples where the optimizer can speed things up if you
ask less, so the ones below are just some examples.
- If there is a covering index (an index that holds all the columns
needed for your query) that it can be used instead of the base table. A
covering index is typically much smaller than the table, so on average
less I/O is required to retrieve the same number of rows
- If a row's size expands to over 4096 bytes, for example because you
just update a varchar column with a bigger string, then the data of this
column will be moved to a separate page. If you do not use this column
in your select query, this extra page does not have to be retrieved
- If you have text, ntext or image column in your table. Their values
are typically (but not always) stored in seperate pages
- If there is an indexed view defined for the table, it might be used
instead of the actual table (provided you are using Enterprise Edition)
One last thing. You are right, that if the storage engine has to
retrieve a page of table data, then (apart from the cases above) most or
all row data is retrieved. However, if the query plan has multiple
operators, all columns that are selected have to be kept in memory and
(possible) processed. This might involve I/O in tempdb. Also, the data
has to be piped to your application. Over a WAN that might cause
(serious) delays.
HTH,
Gert-Jan
fig000 wrote:
> Hi,
> I am a programmer and my understanding of low-level database I/O
> issues is limited. I've just taken a job with a company that is
> currently using classic asp with inline sql. I come from an
> environment with stored procs and I'm used to dealing in full records
> and recordsets. We've been discussing database IO and my colleagues
> here feel that it's necessary with classic asp to only pull in fields
> you need, even if you're working with one record in a table. This,
> they feel, will will cut down on IO time and bandwidth.
> In my reading, I've gotten the impression that the days of worrying
> about how many fields you bring in from one record are in the past
> since I/O involves paging (meaning that more than a couple of fields
> are brought in no matter what) and disks are so fast it doesn't
> matter.
> To support their arguments they've brought up the issue of bringing
> in ntext fields which can slow down an I/0 operation. In this case I
> agree with them but there methodology goes against the whole idea of
> standard I/O concepts (e.g. creating stored procs to read and write to
> at least an entire record).
> I'm not certain of my take on things. I hope that someone might give
> me some guidance and perhaps some articles to read on this subject.
> What we have here is a lot of disparate in-line sql which I feel is
> redudant and not easily modified since it's all over the place (i.e.
> the addtion of one critical field in a table would require changing
> hundreds of isolated inline sql strings).
> Any advice would be appreciated.
> Thanks,
> Fig|||Best practice for me is usually what you said, and what they said.
Return only what you need, and use stored procedures.
On Apr 16, 3:51 pm, "fig000" <neilnewton...@.yahoo.com> wrote:
> Greg,
> Thanks for answering. I can see that there is no simple answer to
> this question. What I wonder then, is how the whole current trend got
> started: creating a stored procedure for each of the four crud
> operations for each table in a database. This is sort of underscored
> by the development of all the wizards (datadapter in 2003 and
> tableadapter in 2005). Are these simply bad practices which simplify
> programming?
> I guess I can see these issues if you are reading a large number of
> records (which would make the use of the ado.net recordsets dangerious
> if not used carefully). But for one record (or a few) wouldn't the
> difference between reading two fields or 50 be minimal in terms of
> database I/O? If I'm right, wouldn't the use of a standardized "select
> *" type of functionality be more convenient from a coding standpoint
> and cause little trouble in terms of database I/O?
> I guess my final question would be: how could possibly put hundreds
> of disparate queries selecting various sets of fields from a table or
> tables, into stored procs? Wouldn't the complexity of that be an
> argument for standardizing the SQL for a table into four simple crud
> queries that could be called from any part of the system. Wouldn't
> that also make modifying the queries (say for adding a new crictical
> field) much easier.
> I admit I don't know much about database internals so please forgive
> my possible ignorance.
> Thanks again for answering this sort of off the wall request. Are
> there any articles or books that might clear this up for me?
> Fig
> On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
>
> <mooregr_deletet...@.greenms.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||"fig000" <neilnewton001@.yahoo.com> wrote in message
news:1176753103.971027.241230@.y80g2000hsf.googlegroups.com...
> Greg,
> Thanks for answering. I can see that there is no simple answer to
> this question. What I wonder then, is how the whole current trend got
> started: creating a stored procedure for each of the four crud
> operations for each table in a database. This is sort of underscored
> by the development of all the wizards (datadapter in 2003 and
> tableadapter in 2005). Are these simply bad practices which simplify
> programming?
Not really sure what you're asking here.

> I guess I can see these issues if you are reading a large number of
> records (which would make the use of the ado.net recordsets dangerious
> if not used carefully). But for one record (or a few) wouldn't the
> difference between reading two fields or 50 be minimal in terms of
> database I/O?
Not necessarily. Let's say you want to read one record.
In that case, (I'm going to assume basic, row, no large blobs), you need to
read one 8K page.
Fair enough. Even if you only need two fields (say a total of 100 bytes)
you're reading an 8K page.
However, consider the situation where each call only returns 1 row, but you
call that code 100 times.
Worse case scenario, you need to read 100 8K pages. (This assumes a select
* from...)
Best case with say a covering index and returning 100 bytes is reading in 2
8K pages.
That's 50 times more effecient.
Now, in the worst case, if the pages are scattered all over the disk, you
might need a physical read for each one. That's 100 physical reads.
In the best case, you need two physical reads and in some cases just ONE
physical read (since the engine will generally read multiple pages in a row
in one pass). Combine that with lack of arm movement and this suddenly
becomes far more effecient.

> If I'm right, wouldn't the use of a standardized "select
> *" type of functionality be more convenient from a coding standpoint
> and cause little trouble in terms of database I/O?
Well if you want a worse case of 100x slower I/O sure that's a little
trouble. :-)
(and note we're assuming you don't have any large blobs returned in a select
* that are thrown away.)
And actually a select * could be far worse than the 100 reads above in some
cases, depending on choices the optimizer will make.

> I guess my final question would be: how could possibly put hundreds
> of disparate queries selecting various sets of fields from a table or
> tables, into stored procs? Wouldn't the complexity of that be an
> argument for standardizing the SQL for a table into four simple crud
> queries that could be called from any part of the system. Wouldn't
> that also make modifying the queries (say for adding a new crictical
> field) much easier.
Well from the above, sounds more like the whole application might need to be
reviewed.
You can get other benefits from stored procs also, query/plan re-use,
protect against SQL injection attacks.

> I admit I don't know much about database internals so please forgive
> my possible ignorance.
> Thanks again for answering this sort of off the wall request. Are
> there any articles or books that might clear this up for me?
Yeah.. Inside SQL Server either for 2000 or 2005 depending on what you're
using.

> Fig
> On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Basic Questions about SQL Server IO Optimization

Hi,
I am a programmer and my understanding of low-level database I/O
issues is limited. I've just taken a job with a company that is
currently using classic asp with inline sql. I come from an
environment with stored procs and I'm used to dealing in full records
and recordsets. We've been discussing database IO and my colleagues
here feel that it's necessary with classic asp to only pull in fields
you need, even if you're working with one record in a table. This,
they feel, will will cut down on IO time and bandwidth.
In my reading, I've gotten the impression that the days of worrying
about how many fields you bring in from one record are in the past
since I/O involves paging (meaning that more than a couple of fields
are brought in no matter what) and disks are so fast it doesn't
matter.
To support their arguments they've brought up the issue of bringing
in ntext fields which can slow down an I/0 operation. In this case I
agree with them but there methodology goes against the whole idea of
standard I/O concepts (e.g. creating stored procs to read and write to
at least an entire record).
I'm not certain of my take on things. I hope that someone might give
me some guidance and perhaps some articles to read on this subject.
What we have here is a lot of disparate in-line sql which I feel is
redudant and not easily modified since it's all over the place (i.e.
the addtion of one critical field in a table would require changing
hundreds of isolated inline sql strings).
Any advice would be appreciated.
Thanks,
Fig"fig000" <neilnewton001@.yahoo.com> wrote in message
news:1176731759.224287.121120@.y5g2000hsa.googlegroups.com...
> Hi,
> I am a programmer and my understanding of low-level database I/O
> issues is limited. I've just taken a job with a company that is
> currently using classic asp with inline sql. I come from an
> environment with stored procs and I'm used to dealing in full records
> and recordsets. We've been discussing database IO and my colleagues
> here feel that it's necessary with classic asp to only pull in fields
> you need, even if you're working with one record in a table. This,
> they feel, will will cut down on IO time and bandwidth.
Generally they're probably correct. If you're suggesting a select * is "ok"
your own example below seems to agree with the problems that might cause.
Also, consider a case where their select statement only brings back 2 fields
from a row, but those 2 fields are in a covering index. In that case, the
IO can be VERY effecient.
If they were bring back additional rows not in the covered index and not
using them, the IO usage can go way up.
Overall though I'd agree, getting rid of inline stuff and replacing with
stored procedures would probably be even better. :-)
> In my reading, I've gotten the impression that the days of worrying
> about how many fields you bring in from one record are in the past
> since I/O involves paging (meaning that more than a couple of fields
> are brought in no matter what) and disks are so fast it doesn't
> matter.
> To support their arguments they've brought up the issue of bringing
> in ntext fields which can slow down an I/0 operation. In this case I
> agree with them but there methodology goes against the whole idea of
> standard I/O concepts (e.g. creating stored procs to read and write to
> at least an entire record).
> I'm not certain of my take on things. I hope that someone might give
> me some guidance and perhaps some articles to read on this subject.
> What we have here is a lot of disparate in-line sql which I feel is
> redudant and not easily modified since it's all over the place (i.e.
> the addtion of one critical field in a table would require changing
> hundreds of isolated inline sql strings).
> Any advice would be appreciated.
> Thanks,
> Fig
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Greg,
Thanks for answering. I can see that there is no simple answer to
this question. What I wonder then, is how the whole current trend got
started: creating a stored procedure for each of the four crud
operations for each table in a database. This is sort of underscored
by the development of all the wizards (datadapter in 2003 and
tableadapter in 2005). Are these simply bad practices which simplify
programming?
I guess I can see these issues if you are reading a large number of
records (which would make the use of the ado.net recordsets dangerious
if not used carefully). But for one record (or a few) wouldn't the
difference between reading two fields or 50 be minimal in terms of
database I/O? If I'm right, wouldn't the use of a standardized "select
*" type of functionality be more convenient from a coding standpoint
and cause little trouble in terms of database I/O?
I guess my final question would be: how could possibly put hundreds
of disparate queries selecting various sets of fields from a table or
tables, into stored procs? Wouldn't the complexity of that be an
argument for standardizing the SQL for a table into four simple crud
queries that could be called from any part of the system. Wouldn't
that also make modifying the queries (say for adding a new crictical
field) much easier.
I admit I don't know much about database internals so please forgive
my possible ignorance.
Thanks again for answering this sort of off the wall request. Are
there any articles or books that might clear this up for me?
Fig
On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> "fig000" <neilnewton...@.yahoo.com> wrote in message
> news:1176731759.224287.121120@.y5g2000hsa.googlegroups.com...
> > Hi,
> > I am a programmer and my understanding of low-level database I/O
> > issues is limited. I've just taken a job with a company that is
> > currently using classic asp with inline sql. I come from an
> > environment with stored procs and I'm used to dealing in full records
> > and recordsets. We've been discussing database IO and my colleagues
> > here feel that it's necessary with classic asp to only pull in fields
> > you need, even if you're working with one record in a table. This,
> > they feel, will will cut down on IO time and bandwidth.
> Generally they're probably correct. If you're suggesting a select * is "ok"
> your own example below seems to agree with the problems that might cause.
> Also, consider a case where their select statement only brings back 2 fields
> from a row, but those 2 fields are in a covering index. In that case, the
> IO can be VERY effecient.
> If they were bring back additional rows not in the covered index and not
> using them, the IO usage can go way up.
> Overall though I'd agree, getting rid of inline stuff and replacing with
> stored procedures would probably be even better. :-)
>
>
> > In my reading, I've gotten the impression that the days of worrying
> > about how many fields you bring in from one record are in the past
> > since I/O involves paging (meaning that more than a couple of fields
> > are brought in no matter what) and disks are so fast it doesn't
> > matter.
> > To support their arguments they've brought up the issue of bringing
> > in ntext fields which can slow down an I/0 operation. In this case I
> > agree with them but there methodology goes against the whole idea of
> > standard I/O concepts (e.g. creating stored procs to read and write to
> > at least an entire record).
> > I'm not certain of my take on things. I hope that someone might give
> > me some guidance and perhaps some articles to read on this subject.
> > What we have here is a lot of disparate in-line sql which I feel is
> > redudant and not easily modified since it's all over the place (i.e.
> > the addtion of one critical field in a table would require changing
> > hundreds of isolated inline sql strings).
> > Any advice would be appreciated.
> > Thanks,
> > Fig
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Fig,
SQL Server uses a cost based optimizer that is getting smarter with each
release. The more to-the-point your query gets, the more options the
optimizer has to achieve this as fast and efficient as possible. If you
don't need all columns of a row, then don't ask for it.
There are many examples where the optimizer can speed things up if you
ask less, so the ones below are just some examples.
- If there is a covering index (an index that holds all the columns
needed for your query) that it can be used instead of the base table. A
covering index is typically much smaller than the table, so on average
less I/O is required to retrieve the same number of rows
- If a row's size expands to over 4096 bytes, for example because you
just update a varchar column with a bigger string, then the data of this
column will be moved to a separate page. If you do not use this column
in your select query, this extra page does not have to be retrieved
- If you have text, ntext or image column in your table. Their values
are typically (but not always) stored in seperate pages
- If there is an indexed view defined for the table, it might be used
instead of the actual table (provided you are using Enterprise Edition)
One last thing. You are right, that if the storage engine has to
retrieve a page of table data, then (apart from the cases above) most or
all row data is retrieved. However, if the query plan has multiple
operators, all columns that are selected have to be kept in memory and
(possible) processed. This might involve I/O in tempdb. Also, the data
has to be piped to your application. Over a WAN that might cause
(serious) delays.
HTH,
Gert-Jan
fig000 wrote:
> Hi,
> I am a programmer and my understanding of low-level database I/O
> issues is limited. I've just taken a job with a company that is
> currently using classic asp with inline sql. I come from an
> environment with stored procs and I'm used to dealing in full records
> and recordsets. We've been discussing database IO and my colleagues
> here feel that it's necessary with classic asp to only pull in fields
> you need, even if you're working with one record in a table. This,
> they feel, will will cut down on IO time and bandwidth.
> In my reading, I've gotten the impression that the days of worrying
> about how many fields you bring in from one record are in the past
> since I/O involves paging (meaning that more than a couple of fields
> are brought in no matter what) and disks are so fast it doesn't
> matter.
> To support their arguments they've brought up the issue of bringing
> in ntext fields which can slow down an I/0 operation. In this case I
> agree with them but there methodology goes against the whole idea of
> standard I/O concepts (e.g. creating stored procs to read and write to
> at least an entire record).
> I'm not certain of my take on things. I hope that someone might give
> me some guidance and perhaps some articles to read on this subject.
> What we have here is a lot of disparate in-line sql which I feel is
> redudant and not easily modified since it's all over the place (i.e.
> the addtion of one critical field in a table would require changing
> hundreds of isolated inline sql strings).
> Any advice would be appreciated.
> Thanks,
> Fig|||Best practice for me is usually what you said, and what they said.
Return only what you need, and use stored procedures.
On Apr 16, 3:51 pm, "fig000" <neilnewton...@.yahoo.com> wrote:
> Greg,
> Thanks for answering. I can see that there is no simple answer to
> this question. What I wonder then, is how the whole current trend got
> started: creating a stored procedure for each of the four crud
> operations for each table in a database. This is sort of underscored
> by the development of all the wizards (datadapter in 2003 and
> tableadapter in 2005). Are these simply bad practices which simplify
> programming?
> I guess I can see these issues if you are reading a large number of
> records (which would make the use of the ado.net recordsets dangerious
> if not used carefully). But for one record (or a few) wouldn't the
> difference between reading two fields or 50 be minimal in terms of
> database I/O? If I'm right, wouldn't the use of a standardized "select
> *" type of functionality be more convenient from a coding standpoint
> and cause little trouble in terms of database I/O?
> I guess my final question would be: how could possibly put hundreds
> of disparate queries selecting various sets of fields from a table or
> tables, into stored procs? Wouldn't the complexity of that be an
> argument for standardizing the SQL for a table into four simple crud
> queries that could be called from any part of the system. Wouldn't
> that also make modifying the queries (say for adding a new crictical
> field) much easier.
> I admit I don't know much about database internals so please forgive
> my possible ignorance.
> Thanks again for answering this sort of off the wall request. Are
> there any articles or books that might clear this up for me?
> Fig
> On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
>
> <mooregr_deletet...@.greenms.com> wrote:
> > "fig000" <neilnewton...@.yahoo.com> wrote in message
> >news:1176731759.224287.121120@.y5g2000hsa.googlegroups.com...
> > > Hi,
> > > I am a programmer and my understanding of low-level database I/O
> > > issues is limited. I've just taken a job with a company that is
> > > currently using classic asp with inline sql. I come from an
> > > environment with stored procs and I'm used to dealing in full records
> > > and recordsets. We've been discussing database IO and my colleagues
> > > here feel that it's necessary with classic asp to only pull in fields
> > > you need, even if you're working with one record in a table. This,
> > > they feel, will will cut down on IO time and bandwidth.
> > Generally they're probably correct. If you're suggesting a select * is "ok"
> > your own example below seems to agree with the problems that might cause.
> > Also, consider a case where their select statement only brings back 2 fields
> > from a row, but those 2 fields are in a covering index. In that case, the
> > IO can be VERY effecient.
> > If they were bring back additional rows not in the covered index and not
> > using them, the IO usage can go way up.
> > Overall though I'd agree, getting rid of inline stuff and replacing with
> > stored procedures would probably be even better. :-)
> > > In my reading, I've gotten the impression that the days of worrying
> > > about how many fields you bring in from one record are in the past
> > > since I/O involves paging (meaning that more than a couple of fields
> > > are brought in no matter what) and disks are so fast it doesn't
> > > matter.
> > > To support their arguments they've brought up the issue of bringing
> > > in ntext fields which can slow down an I/0 operation. In this case I
> > > agree with them but there methodology goes against the whole idea of
> > > standard I/O concepts (e.g. creating stored procs to read and write to
> > > at least an entire record).
> > > I'm not certain of my take on things. I hope that someone might give
> > > me some guidance and perhaps some articles to read on this subject.
> > > What we have here is a lot of disparate in-line sql which I feel is
> > > redudant and not easily modified since it's all over the place (i.e.
> > > the addtion of one critical field in a table would require changing
> > > hundreds of isolated inline sql strings).
> > > Any advice would be appreciated.
> > > Thanks,
> > > Fig
> > --
> > Greg Moore
> > SQL Server DBA Consulting Remote and Onsite available!
> > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html- Hide quoted text -
> - Show quoted text -|||"fig000" <neilnewton001@.yahoo.com> wrote in message
news:1176753103.971027.241230@.y80g2000hsf.googlegroups.com...
> Greg,
> Thanks for answering. I can see that there is no simple answer to
> this question. What I wonder then, is how the whole current trend got
> started: creating a stored procedure for each of the four crud
> operations for each table in a database. This is sort of underscored
> by the development of all the wizards (datadapter in 2003 and
> tableadapter in 2005). Are these simply bad practices which simplify
> programming?
Not really sure what you're asking here.
> I guess I can see these issues if you are reading a large number of
> records (which would make the use of the ado.net recordsets dangerious
> if not used carefully). But for one record (or a few) wouldn't the
> difference between reading two fields or 50 be minimal in terms of
> database I/O?
Not necessarily. Let's say you want to read one record.
In that case, (I'm going to assume basic, row, no large blobs), you need to
read one 8K page.
Fair enough. Even if you only need two fields (say a total of 100 bytes)
you're reading an 8K page.
However, consider the situation where each call only returns 1 row, but you
call that code 100 times.
Worse case scenario, you need to read 100 8K pages. (This assumes a select
* from...)
Best case with say a covering index and returning 100 bytes is reading in 2
8K pages.
That's 50 times more effecient.
Now, in the worst case, if the pages are scattered all over the disk, you
might need a physical read for each one. That's 100 physical reads.
In the best case, you need two physical reads and in some cases just ONE
physical read (since the engine will generally read multiple pages in a row
in one pass). Combine that with lack of arm movement and this suddenly
becomes far more effecient.
> If I'm right, wouldn't the use of a standardized "select
> *" type of functionality be more convenient from a coding standpoint
> and cause little trouble in terms of database I/O?
Well if you want a worse case of 100x slower I/O sure that's a little
trouble. :-)
(and note we're assuming you don't have any large blobs returned in a select
* that are thrown away.)
And actually a select * could be far worse than the 100 reads above in some
cases, depending on choices the optimizer will make.
> I guess my final question would be: how could possibly put hundreds
> of disparate queries selecting various sets of fields from a table or
> tables, into stored procs? Wouldn't the complexity of that be an
> argument for standardizing the SQL for a table into four simple crud
> queries that could be called from any part of the system. Wouldn't
> that also make modifying the queries (say for adding a new crictical
> field) much easier.
Well from the above, sounds more like the whole application might need to be
reviewed.
You can get other benefits from stored procs also, query/plan re-use,
protect against SQL injection attacks.
> I admit I don't know much about database internals so please forgive
> my possible ignorance.
> Thanks again for answering this sort of off the wall request. Are
> there any articles or books that might clear this up for me?
Yeah.. Inside SQL Server either for 2000 or 2005 depending on what you're
using.
> Fig
> On Apr 16, 10:58 am, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>> "fig000" <neilnewton...@.yahoo.com> wrote in message
>> news:1176731759.224287.121120@.y5g2000hsa.googlegroups.com...
>> > Hi,
>> > I am a programmer and my understanding of low-level database I/O
>> > issues is limited. I've just taken a job with a company that is
>> > currently using classic asp with inline sql. I come from an
>> > environment with stored procs and I'm used to dealing in full records
>> > and recordsets. We've been discussing database IO and my colleagues
>> > here feel that it's necessary with classic asp to only pull in fields
>> > you need, even if you're working with one record in a table. This,
>> > they feel, will will cut down on IO time and bandwidth.
>> Generally they're probably correct. If you're suggesting a select * is
>> "ok"
>> your own example below seems to agree with the problems that might cause.
>> Also, consider a case where their select statement only brings back 2
>> fields
>> from a row, but those 2 fields are in a covering index. In that case,
>> the
>> IO can be VERY effecient.
>> If they were bring back additional rows not in the covered index and not
>> using them, the IO usage can go way up.
>> Overall though I'd agree, getting rid of inline stuff and replacing with
>> stored procedures would probably be even better. :-)
>>
>>
>> > In my reading, I've gotten the impression that the days of worrying
>> > about how many fields you bring in from one record are in the past
>> > since I/O involves paging (meaning that more than a couple of fields
>> > are brought in no matter what) and disks are so fast it doesn't
>> > matter.
>> > To support their arguments they've brought up the issue of bringing
>> > in ntext fields which can slow down an I/0 operation. In this case I
>> > agree with them but there methodology goes against the whole idea of
>> > standard I/O concepts (e.g. creating stored procs to read and write to
>> > at least an entire record).
>> > I'm not certain of my take on things. I hope that someone might give
>> > me some guidance and perhaps some articles to read on this subject.
>> > What we have here is a lot of disparate in-line sql which I feel is
>> > redudant and not easily modified since it's all over the place (i.e.
>> > the addtion of one critical field in a table would require changing
>> > hundreds of isolated inline sql strings).
>> > Any advice would be appreciated.
>> > Thanks,
>> > Fig
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html