Latin1_General_BIN vs Latin1_General_BIN2 in Script

Hi John
No, with Latin1_General_CI_AS I not have problems, but I dont test with
others Collations and the problem is in db not in the tables.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Does the same thing happen with Latin1_General_CI_AS? Does this happen with
> a subset of the tables etc?
> John
> "sqlextreme" wrote:
Hi
How are you generating this script?
John
"sqlextreme" wrote:
[vbcol=seagreen]
> Hi John
> No, with Latin1_General_CI_AS I not have problems, but I dont test with
> others Collations and the problem is in db not in the tables.
> "John Bell" wrote:
|||In Management Studio-> Select de Name db -> Tasks -> generate scripts -> db
and etc. for all objects
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> How are you generating this script?
> John
> "sqlextreme" wrote:
|||Hi
I have no problems generating a script for the following database:
CREATE DATABASE BIN_DB
COLLATE LATIN1_GENERAL_BIN2
USE BIN_DB
GO
CREATE TABLE tbl1 ( id int not null identity, col1 varchar(10) )
GO
CREATE TABLE [dbo].[tbl2](
[id] [int] IDENTITY(1,1) NOT NULL,
[col1] [varchar](10) COLLATE LATIN1_GENERAL_BIN2 NULL
)
GO
USE TEMPDB
GO
John
"sqlextreme" wrote:
[vbcol=seagreen]
> In Management Studio-> Select de Name db -> Tasks -> generate scripts -> db
> and etc. for all objects
> "John Bell" wrote:
|||Is strange, if you make script for all objects this is the result:
Generate Script Progress
- Determining objects in database 'BIN_DB' that will be scripted. (Error)
Messages
* Exception has been thrown by the target of an invocation. (mscorlib)
ADDITIONAL INFORMATION:
There is already an open DataReader associated with this Command which must
be closed first. (System.Data)
but, if you make script by next and next for all objects this is the result:
Generate Script Progress
- Determining objects in database 'BIN_DB' that will be scripted. (Success)
- dbo.tbl1 (Success)
- dbo.tbl2 (Success)
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I have no problems generating a script for the following database:
> CREATE DATABASE BIN_DB
> COLLATE LATIN1_GENERAL_BIN2
> USE BIN_DB
> GO
> CREATE TABLE tbl1 ( id int not null identity, col1 varchar(10) )
> GO
> CREATE TABLE [dbo].[tbl2](
> [id] [int] IDENTITY(1,1) NOT NULL,
> [col1] [varchar](10) COLLATE LATIN1_GENERAL_BIN2 NULL
> )
> GO
> USE TEMPDB
> GO
> John
> "sqlextreme" wrote:
|||Hi
It does seem like this is a bug, selecting "Script all objects in the
selected database" will also produce the error on my system. There seems to
be a few similar errors logged on connect such as
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127175
although that is closed and supposed to be fixed before the version I am
using (9.00.2047.00).
John
"sqlextreme" wrote:
[vbcol=seagreen]
> Is strange, if you make script for all objects this is the result:
> Generate Script Progress
> - Determining objects in database 'BIN_DB' that will be scripted. (Error)
> Messages
> * Exception has been thrown by the target of an invocation. (mscorlib)
> --
> ADDITIONAL INFORMATION:
> There is already an open DataReader associated with this Command which must
> be closed first. (System.Data)
> but, if you make script by next and next for all objects this is the result:
> Generate Script Progress
> - Determining objects in database 'BIN_DB' that will be scripted. (Success)
> - dbo.tbl1 (Success)
> - dbo.tbl2 (Success)
>
>
>
>
> "John Bell" wrote:
|||..I installed a new instance of SQL Server 2005 with Latin1_General_BIN2 and
later I created a db with Latin1_General_BIN2 and I could not create script.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> It does seem like this is a bug, selecting "Script all objects in the
> selected database" will also produce the error on my system. There seems to
> be a few similar errors logged on connect such as
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127175
> although that is closed and supposed to be fixed before the version I am
> using (9.00.2047.00).
> John
> "sqlextreme" wrote:
|||Hi
I have logged this at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=248724 where you can vote for the issue.
John
"sqlextreme" wrote:
[vbcol=seagreen]
> .I installed a new instance of SQL Server 2005 with Latin1_General_BIN2 and
> later I created a db with Latin1_General_BIN2 and I could not create script.
> "John Bell" wrote:
|||Hi,
Ok, thank for your help.
Cristián
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I have logged this at
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=248724 where you can vote for the issue.
> John
> "sqlextreme" wrote:
sql

Latency In Transactional Replication

During last couple of weeks some new Tables & Fields were added. SO both the
time i have to re-intialize the Replication. my question is about LATENCY
where i have seen for both the subscriber(SUB) for SUB-1 is 3.5 Hours &
SUB-2 is 21 hrs. Size of the PUBLISHER DB is 18GB. AS Latency are in
Millisecs i have mentioned in HOURS for better understanding. i want to know
how i can resolve this issue as every time when new table or fields are
added it takes 21-22 hours to complete Replication Re-intialization process.
Any help will be highly appreciated. Thanking you in advance.
Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
changing a PK.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||TWO new Tables were added with unique key for couple of fields & were
not going to be use in Transactional Replication due to limitation that
each table needs to have at least one PK. and this 2 tables as no PK.
So using sp_repladdcolumn will not help, i maybe wrong.
please correct me if i am wrong as i am not SQL expert. again any help
& to correct me will be appreciated.
Paul Ibison wrote:
> Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
> changing a PK.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear Mr. Paul:
I have replied back with details & did see your
feedback. i will appreciate if you please kindly reply me at your
earliest & doing so will be highly appreciated.
thanks
Paul Ibison wrote:
> Now I'm confused - if you're adding tables to a publication this is
> supported and adding columns to an already published article is supported,
> but this seems to be not relevant to your case somehow. Is the problem that
> you want to add tables which don't have a PK? This is definitely
> unsupported, but you already know that, so please can you add a few more
> details about what it is that you want to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

last time a table was used

Any way without a trigger to determine the last time a table was accessed? we have duplicate tables accross some databases, and suspect we don't need a few of them. ;)

They won't let me change the names and see who squeals.

Sql profiler?!|||

Yeah, forgot to mention I only have developer rights.

<<sigh>>

I was hoping there was an additional magic microsoft faerie out there that would somehow know.

sql

last read/write time of table

Is there a way to get the last read/write time of a table?

I want to have a few tables, but only allow them to exist if they have been used in the last 30 days. I want to set up a "purge" job to clear out any tables that have not been used in 30 days.

I think that unless you were to add a datetime column to each table, that information will be difficult to obtain.

For you purposes, it may be useful to add a datetime column, DEFAULT getdate(). If the application never provides a value, the server will always provide the current date/time on entry. UPDATEs would require a trigger.

You 'could' add a trigger to each table that updates a single field in a maintenance table. -Probably not too good of an idea though...

|||In addition to Arnies response and for the sake that you really need to know the last modification date, you could use a log reader to determine the last modification date. SQL Server does not provide an easy way to read the logs, therefore you would probably need to use a thrid party component.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Last Modified Field

I'm creating a last modified field that I'll be putting into several of my tables in my current database and in many more to come so I want to get the most optimal way of doing this established so I don't end up propogating a problem. The field will be updated with the current time and date each time the row is modified. I created a trigger to do this, but I don't know if this is the best way. I'm not real experienced with MS SQL so I'm certainly open to suggestions. Here's the trigger code I have now.

UPDATE s
SET LastModified = getdate()
FROM secUser s
join inserted i on s.UID = i.UID

Any suggestions would be appreciated.

Thanks,
Nate BaxleyIn this case, the best choice is instead of update trigger.

Good luck!

Last Modified Date

I have a number of tables in a database that have 0 records. Some tables are
purged frequently. I am trying to determine which tables are truly in use
versus those tables that are not being used at all. Where or how might I
obtain the last date/time a table was modifed?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robert,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in the tables or use triggers. C2 is probably overkill for
something like this.
Read these:
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx
and
http://www.akadia.com/services/sqlsrv_table_auditing.html
HTH
Jerry
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:55d4d8776aa3c@.uwe...
>I have a number of tables in a database that have 0 records. Some tables
>are
> purged frequently. I am trying to determine which tables are truly in use
> versus those tables that are not being used at all. Where or how might I
> obtain the last date/time a table was modifed?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1

Last Modified Date

I have a number of tables in a database that have 0 records. Some tables are
purged frequently. I am trying to determine which tables are truly in use
versus those tables that are not being used at all. Where or how might I
obtain the last date/time a table was modifed?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robert,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in the tables or use triggers. C2 is probably overkill for
something like this.
Read these:
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
and
http://www.akadia.com/services/sqls...e_auditing.html
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d4d8776aa3c@.uwe...
>I have a number of tables in a database that have 0 records. Some tables
>are
> purged frequently. I am trying to determine which tables are truly in use
> versus those tables that are not being used at all. Where or how might I
> obtain the last date/time a table was modifed?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1

Last Modified Date

I have a number of tables in a database that have 0 records. Some tables are
purged frequently. I am trying to determine which tables are truly in use
versus those tables that are not being used at all. Where or how might I
obtain the last date/time a table was modifed?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robert,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in the tables or use triggers. C2 is probably overkill for
something like this.
Read these:
http://www.microsoft.com/technet/sec.../sql2kaud.mspx
and
http://www.akadia.com/services/sqlsr..._auditing.html
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d4d8776aa3c@.uwe...
>I have a number of tables in a database that have 0 records. Some tables
>are
> purged frequently. I am trying to determine which tables are truly in use
> versus those tables that are not being used at all. Where or how might I
> obtain the last date/time a table was modifed?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1

Last login in sql server

Hello,
Is there any way to tell when a user last logged into sql server/system
without auditing set? Are there any system tables or sp that will give me
that information? Thanks.
--
New SQL Server DBANewbie,
For current processes:
SELECT LOGINAME,LOGIN_TIME FROM MASTER..SYSPROCESSES
For logins in the past...not that I'm aware of without using built-in
auditing, a custom auditing solution or third-party auditing software. A
shot in the dark, you might be able to determine this by evaulating the
t-log backups in the past with a third-party log viewer tool.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:7692EDE5-82DC-4538-91A0-0E380D41862F@.microsoft.com...
> Hello,
> Is there any way to tell when a user last logged into sql server/system
> without auditing set? Are there any system tables or sp that will give me
> that information? Thanks.
> --
> New SQL Server DBA

Last login in sql server

Hello,
Is there any way to tell when a user last logged into sql server/system
without auditing set? Are there any system tables or sp that will give me
that information? Thanks.
--
New SQL Server DBANewbie,
For current processes:
SELECT LOGINAME,LOGIN_TIME FROM MASTER..SYSPROCESSES
For logins in the past...not that I'm aware of without using built-in
auditing, a custom auditing solution or third-party auditing software. A
shot in the dark, you might be able to determine this by evaulating the
t-log backups in the past with a third-party log viewer tool.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:7692EDE5-82DC-4538-91A0-0E380D41862F@.microsoft.com...
> Hello,
> Is there any way to tell when a user last logged into sql server/system
> without auditing set? Are there any system tables or sp that will give me
> that information? Thanks.
> --
> New SQL Server DBA

Last login in sql server

Hello,
Is there any way to tell when a user last logged into sql server/system
without auditing set? Are there any system tables or sp that will give me
that information? Thanks.
New SQL Server DBA
Newbie,
For current processes:
SELECT LOGINAME,LOGIN_TIME FROM MASTER..SYSPROCESSES
For logins in the past...not that I'm aware of without using built-in
auditing, a custom auditing solution or third-party auditing software. A
shot in the dark, you might be able to determine this by evaulating the
t-log backups in the past with a third-party log viewer tool.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:7692EDE5-82DC-4538-91A0-0E380D41862F@.microsoft.com...
> Hello,
> Is there any way to tell when a user last logged into sql server/system
> without auditing set? Are there any system tables or sp that will give me
> that information? Thanks.
> --
> New SQL Server DBA

Last Date Used

I am trying to generate a report listing data base and tables within the
data base when last accessed, updated or modified. Does anyone know of
a tool that performs this function?
Ernie
A Plan without Action is a DayDream
Action without a Plan is a Nightmare
*** Sent via Developersdex http://www.codecomments.com ***
Hi,
SQL Server will not store this information. For doing this you need to
enable the profiler, store the output into a table and then do the
analysis manually to get the report.
Thanks
Hari
SQL Server MVP
"Ernie" <nospan@.devdex.com> wrote in message
news:OUTJ%23INUFHA.584@.TK2MSFTNGP15.phx.gbl...
> I am trying to generate a report listing data base and tables within the
> data base when last accessed, updated or modified. Does anyone know of
> a tool that performs this function?
> Ernie
> A Plan without Action is a DayDream
> Action without a Plan is a Nightmare
> *** Sent via Developersdex http://www.codecomments.com ***

Largest tables

Hi,
Is there an easier way to determine which tables in a db are the largest other than running sp_spaceused for each table? Interested mainly in physical size.
ThanksRun DBCC UPDATEUSAGE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_24rp.asp) to bring your statistics up to date, then you can use:SELECT so.name, 8 * Sum(si.reserved)
FROM dbo.sysobjects AS so
JOIN dbo.sysindexes AS si
ON (si.id = so.id)
ORDER BY 2 desc, 1for a good estimate.

-PatP

Large views vs multiple small views

Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?

It depends on how often you use the views and how much of data you need from all the 10 tables. If majority of your usage is only couple of columns from couple of tables then smaller view with only required tables will be more efficient as opposed to one giant view that is called every time even if most of the info the view returns is not required.

Large view problem

I have a considerably large view that is pulling data from tables and other view, using user defined functions, and using case statements. This view is taking a lot of time to load.

I was under the impression that SQL server keeps the views uptodate, so selecting data from them is as fast as selecting from a table. It now seems like SQL server rebuilds the view evertime something accesses it.

Can someone please tell me more on this. I am now having to re-write everything :-(

Thanks Jag

Shameless bump|||Do you have indexes on the columns being referenced in the view? Are you filtering the view with a WHERE clause and do they also have indexes and how up to date are they?|||

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

The view is being filtered by a where clause. However, the problem is actually building the view. When I use the query analyzer to select all the rows, it still takes 30-40 seconds to return just 252 rows !!!!

What was happening was that I was using a number of view to pull out the last data being entered into different areas. These view where then combined into this large view. I also used user defined functions to pull out other data.

I have had to change this to use triggers and store the data elsewhere. This has cut the time down considerable, but I an still wondering why the problem existed in the first place.

|||

jagdipa:

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

Perhaps its time to look into it now..There are some very good articles @.http://www.sql-server-performance.com/articles.asp

Large Transacation Log even with Simple Recovery

Greetings,
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Thank you for this information. It is very helpful!

>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>

Large Transacation Log even with Simple Recovery

Greetings,
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!
hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thank you for this information. It is very helpful!

>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>

Large Tables, Inserts, and selecting data.

Hello,
I'd like to point out that, obviously, I'm not a Database Admin - but I have
read a fair amount on the topic and examined several case studies similiar t
o
this issue.
Is there a recommended size limit for the size of a table in SQL Server
2000? We have a system that is gathering test data in near real time that is
then inserted into SQL and our admin indicated that after 'a while' inserts
were slowing down as the table got bigger. If the table were properly
normalized and indexed, would the size of the table even matter?
The solution currently implemented is that a new table is created every
hour. Each of these tables contain the same schema, but represent data from
a
specific hour of the day. In other words, you end up with 24 tables per day.
Obviously there are other tables now involved that have to track all of this
and the SQL necessary to select the data appears needlessly complex.
So my question, (along with the one above) is quite simple. Is this a common
practice? Are there addtional case studies, web sites, or whatnot that I can
review before participating in the next design meeting?
Thanks for reading!
Chris>> Is there a recommended size limit for the size of a table in SQL Server
No. The physical resources at your disposal determine the table size
In general, no.
That sounds like a poor approach. Other than increasing your maintenance
overhead, prone-ness to error and overall complexity, it seems to offer
nothing beneficial.
Some folks do it, assuming it is somehow simpler to use several smaller
tables instead of a single one. Such systems are often poorly designed
without any consideration to good logical principles and often fall apart
due to increased complexity and lack of scalability options.
The general recommended practice is to use a single table with an attribute
to distingush the individual sets of test data gathered over each hour,
perhaps using a temporal datatype.
Anith|||From a client (consuming) standpoint, your comments make a lot of sense to m
e
as I feel the same, however my lack of qualifications vs the db admin is
proving difficult.
Very good suggestion attributing the data - I'll be sure to bring this up at
our next meeting.
Are there any other suggestions out there? I just came from another meeting
with the DB folks and they are adamant that when the table becomes 'big'
inserts slow down... that does not make sense to me. The topic was then abou
t
indexing and about how indexing was going to kill the insert process as well
.
I think I'm missing something... thank you for the comments, I'll be looking
forward to more.
Chris
"Anith Sen" wrote:

> No. The physical resources at your disposal determine the table size
>
> In general, no.
>
> That sounds like a poor approach. Other than increasing your maintenance
> overhead, prone-ness to error and overall complexity, it seems to offer
> nothing beneficial.
>
> Some folks do it, assuming it is somehow simpler to use several smaller
> tables instead of a single one. Such systems are often poorly designed
> without any consideration to good logical principles and often fall apart
> due to increased complexity and lack of scalability options.
> The general recommended practice is to use a single table with an attribut
e
> to distingush the individual sets of test data gathered over each hour,
> perhaps using a temporal datatype.
> --
> Anith
>
>|||I'd had thought this discussion would have generated more interest.
Is there a group better suited to this question?
Thanks,
Chris
"Chris Keller" wrote:
> From a client (consuming) standpoint, your comments make a lot of sense to
me
> as I feel the same, however my lack of qualifications vs the db admin is
> proving difficult.
> Very good suggestion attributing the data - I'll be sure to bring this up
at
> our next meeting.
> Are there any other suggestions out there? I just came from another meetin
g
> with the DB folks and they are adamant that when the table becomes 'big'
> inserts slow down... that does not make sense to me. The topic was then ab
out
> indexing and about how indexing was going to kill the insert process as we
ll.
> I think I'm missing something... thank you for the comments, I'll be looki
ng
> forward to more.
> Chris
>
> "Anith Sen" wrote:
>|||> >> The solution currently implemented is that a new table is created every
> That sounds like a poor approach. Other than increasing your maintenance
> overhead, prone-ness to error and overall complexity, it seems to offer
> nothing beneficial.
>
> Some folks do it, assuming it is somehow simpler to use several smaller
> tables instead of a single one. Such systems are often poorly designed
> without any consideration to good logical principles and often fall apart
> due to increased complexity and lack of scalability options.
There are, of course, exceptions to this; you can get a performasnce
boost from using a properly-indexed partiioned design in an OLAP
environment if your data influx is very large. We have a single
partioned view containing 90 days worth of data (split into daily
tables), containing nearly 60 million rows of data. By using a
partioned view, index ss are very effecient.
This approach is very effecient on smaller servers; if I had quad
processors and high-speed drives and lots of RAM, I probably could have
gotten away with a single table.
I would NOT recommend this approach unless you have lots of data to
deal with, but I don't think that it's necessarily a bad design as
suggested in the previous quote.
Stu|||>> I would NOT recommend this approach unless you have lots of data to deal
Regarding exceptions, I agree. Logical design is generally done with data
integrity as the primary goal. As you said, exceptions do exist and are
mostly done for specific purposes on a case by case basis.
For instance, partitioned tables/views are often implemented for performance
reasons; constraints must be put in place for preventing data overlaps;
while multiple views are maintained by the system they logically appears as
a single table to the external user.
Anith|||You mention that indexed search is very fast for your solution - can you
define fast?
What about insert performance on indexed tables? Do you see an improvement
by having your data spread across multiple tables? That is a big question
that I have, specifically the argument is that with a large indexed table
that inserts get slower in relation to the size. I don't see why that would
be the case - but perhaps I'm misisng something.
Chris
"Stu" wrote:

> There are, of course, exceptions to this; you can get a performasnce
> boost from using a properly-indexed partiioned design in an OLAP
> environment if your data influx is very large. We have a single
> partioned view containing 90 days worth of data (split into daily
> tables), containing nearly 60 million rows of data. By using a
> partioned view, index ss are very effecient.
> This approach is very effecient on smaller servers; if I had quad
> processors and high-speed drives and lots of RAM, I probably could have
> gotten away with a single table.
> I would NOT recommend this approach unless you have lots of data to
> deal with, but I don't think that it's necessarily a bad design as
> suggested in the previous quote.
> Stu
>|||Our end users analyze firewall events, which can range from 1 event per
incident to hundreds of thousands of events per incident. On average,
they're pulling back 5000 records in milliseconds; note that's 5000
records out of 60,000,000.
INSERTs will slow down in relation to size if your database is having
to grow to accomodate them, especially if your indexes are physically
located on the same drive as your data. However, partitioning your
data is typically more useful during data SELECTion rather than
INSERTing, because you're providing the optimizer hints by which to
narrow down the s.
We also use the partitioning to archive data; on a daily basis, I spin
off the data older than 90 days to an archive db (which is partitioned
on a monthly basis). When a month of data is "closed", we back that
data up to a portable hard drive, which then goes to storage. That
way, we can retrieve data if we need it, but it's not sucking up disk
space on my servers.
Good references on partitioned views:
http://www.sqlteam.com/Item.ASP?ItemID=684
http://www.windowsitpro.com/SQLServ.../8234/8234.html
Stu|||Thank you for the information Stu - I appreciate it.
I should point out that the solution (as currently implemented) is not
partitioned at all. Tables are created in the same database on the same
server - hourly. And I will say that we are not seeing anything simliar to
the perf you are realizing as it can take up to a minute to retrieve 10k
rows...
Looking at the SQL, it appears that most of the work is being done simply
trying to figure out what tables are involved - it is crazy and convuluted
and therefore I'm looking at what other folks have done to see if I can't
persuade folks to change.
Chris
"Stu" wrote:

> Our end users analyze firewall events, which can range from 1 event per
> incident to hundreds of thousands of events per incident. On average,
> they're pulling back 5000 records in milliseconds; note that's 5000
> records out of 60,000,000.
> INSERTs will slow down in relation to size if your database is having
> to grow to accomodate them, especially if your indexes are physically
> located on the same drive as your data. However, partitioning your
> data is typically more useful during data SELECTion rather than
> INSERTing, because you're providing the optimizer hints by which to
> narrow down the s.
> We also use the partitioning to archive data; on a daily basis, I spin
> off the data older than 90 days to an archive db (which is partitioned
> on a monthly basis). When a month of data is "closed", we back that
> data up to a portable hard drive, which then goes to storage. That
> way, we can retrieve data if we need it, but it's not sucking up disk
> space on my servers.
> Good references on partitioned views:
> http://www.sqlteam.com/Item.ASP?ItemID=684
> http://www.windowsitpro.com/SQLServ.../8234/8234.html
> Stu
>|||The nice thing about partitioned views is that the optimizer knows
where the data comes from, and where it belongs, since it uses mutually
exclusive check constraints to place the data. All of our SQL
statements are run against the view; in other words, we'll issue a
command like:
SELECT Columns
FROM View
WHERE CheckColumn = 'some value'
INSERTS and DELETES and UPDATES are also performed on the view itself,
eliminating the need to figure out where the source table is located.
We create tables daily all on the same server; hourly seems a bit much
unless you're dealing with a heck of a lot of data.

Large table/slow query/ can performance be improved?

I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table
data moving across the network and improving performance (woefully slow
about 6 hours)?"Robert" <stop.spam@.whitehouse.gov> wrote in message
news:HLFKqK.MqD@.news.boeing.com...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform
more
> of the work on the server there by minimizing the amount of extraneous
table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?

Hmm, I haven't touched Access in years, but I recall it supporting what I
think it called Pass Thru Queries?

Or another option (perhaps ultimately your best one) is rewrite it as a
stored proc on the server.

If it's taking 6 hours, unless you're joining multigig table and doing
something like outer joins, I've got to imagine you're right, it's Access
trying to process things locally.
|||Hi,

These are some tips from my side.

1. Put indexes on column that is frequently used in query. This will
help database engine to search data quickly.
2. If the Recordset has less than 500 records, then the snapshot
Recordset will be faster than the dynaset Recordset.
3. Make query selection/criteria so as resultset will be small.

Finally, can you elaborate/post what exactly u r trying to do and
achive.

Amit

"Robert" <stop.spam@.whitehouse.gov> wrote in message news:<HLFKqK.MqD@.news.boeing.com>...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform more
> of the work on the server there by minimizing the amount of extraneous table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?|||As long as you're not using any client-side functions (Access or user-defined),
you should be able to make this into a Pass-Through query.

1) Copy the SQL Statement to the clipboard (or better yet, to Notepad)

2) In Access, make a new query, but don't choose any tables.

3) Choose Query/SQL Specific>Pass-Through

4) Paste the SQL Statement in there

5) Choose View/Properties

6) Set the Connect string (and ODBC Timeout!) appropriately

Run the query.

I have never seen an Access query take 6 hours to run...and I have an Access
database linked to over 68 MILLION records. Admittedly, I would never even WAIT
that long to find out if it could ever complete!

The slowest Access queries I've seen are the ones that I call "Query of a query
of a query...etc." with all records included the whole time, and a criteria at
the end; especially if Access or user-defined (VBA) function are invovled!

You really should learn the advantages of SQL Server's "SQL langauange" vs
Access. My favorite example is the FULL OUTER JOIN...with one statement in SQL
Server you get both sets of records from the eaither side of join whether or
not they match...can't do that in Access without making three queries! (OUTER,
Non-Match OUTER, then UNION)

Althogh the IIF in Access is convenient, it insists on evaluating both
outcomes...SQL Server's "CASE" construct makes much more sense.

If your query DOES use Access and/or user-defined VBA Functions, then you
should have the whole process converted to a stored procedure, and the use a
Pass-Through query to call the Stored Procedure. In all seriousness, EVERY
query you need should be converted to paramaterized Stored Procedures, and
called via Pass-Through...this is the best way to optimize performance.

CAVEAT: Pass-Through queries cannot be used as record sources for
linkchild/master situations (That's a JET-only feature), but you can always
program the same functionality yourself!

I'd be interested in seeing the SQL Statement of the 6-hr query.

Large table management and Partitioned Views

Just trying to get feedback from people on their practical experience with
managing large tables (>250mil rows). Has anyone used Partitioned Views and
how do they work? Any performance advantage? If not partitioned views, is
there anything else one can do in a large enterprise environment to manage
large tables?
Hi,
from my experience, Partitioned Views will actually reduce IO read (physical
read and read-ahead read).
it depends on how you split your table and file group.
Leo
"LC" wrote:

> Just trying to get feedback from people on their practical experience with
> managing large tables (>250mil rows). Has anyone used Partitioned Views and
> how do they work? Any performance advantage? If not partitioned views, is
> there anything else one can do in a large enterprise environment to manage
> large tables?
|||Thanks Leo,
Is the reduction in IO reads due to the fact the tables are split
up/smaller? Are there any other benefits or caveats to moving to this type
of architecture?
"Leo Leong" wrote:
[vbcol=seagreen]
> Hi,
> from my experience, Partitioned Views will actually reduce IO read (physical
> read and read-ahead read).
> it depends on how you split your table and file group.
> Leo
> "LC" wrote:
|||before I implemented it, i did a testing in a test environment.
i split a table into 6 where each table is stored in separate data file.
the partition key for the table is TYPR, let say.
so, when i ran a query with "SET STATISTIC IO ON" to select record based on
TYPE = "A", i found that SQL Server will only read the data from the table
that stores TYPE = "A" only.
of course, when the table is split, data file that stores the child tables
is smaller. so, relatively, it will fasten the process of searching a record.
another benefit of partitioned view is you can do distributed database
environment. that means, those child tables can be stored in > 1 database
servers.
Leo
"LC" wrote:
[vbcol=seagreen]
> Thanks Leo,
> Is the reduction in IO reads due to the fact the tables are split
> up/smaller? Are there any other benefits or caveats to moving to this type
> of architecture?
> "Leo Leong" wrote: