I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Tuesday, March 27, 2012
field <long text>
I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft
> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft
> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455
field <long text>
I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455
Few Questions
Hi,
I have few questions about SQL Server, let me mention here..
How to generate XML File from SQL Server? Is there anyway can we generate directly from Query?
What are Temporary tables (Regular & Global) and where do we need them in Real Time?
What are BCP Statements? where do we need them?
How to pass XML Document to Proc, and insert the data, any example?
Types of Triggers?
Shink data, what is that?
What T-Read, Uncommitted read?
What is Replication ?
Thanks
Seshu
hey this is not few... hahaha
How to generate XML File from SQL Server? Is there anyway can we generate directly from Query?
you can use the for XML clause
example :
select * from employees for xml auto
What are Temporary tables (Regular & Global) and where do we need them in Real Time?
these are temporary table and are use to store data that still needs to be processed or
processed information that are too large to be return via a regular variable. temporary tables
performs like a regular table except that they are automatically destroyed when there are no more
connection referencing it. Regular temp tables as ( represent by tablename #temp, single pound sign) as oppose to
global temptable (which are named ##temp, using double pound sign) are visible only to the connection
that created it while Global temp tables are visible to any connection
What are BCP Statements? where do we need them?
bulk copy program is a Dos based interface for loading multiple records from files(CSV,stc) using bcp formats
How to pass XML Document to Proc, and insert the data, any example?in 2005 XML datatype can be used as parameters in table definition and as variables. you can insert an XML or fragment of XML to an XML columns. You can see my blogs for some examples
Types of Triggers?insert, update, delete, instead of, DDL triggers
Shink data, what is that?
This is used to compress the data and release unused spaces use by the database. you can also use Shrinkfile to individually shrink the database
What T-Read, Uncommitted read?
looks like this topic belongs to serialization and locking. this is how sql server is going to read the data
What is Replication ?
replication is copying and synchronizing data to another server
Your homework is quite long hahaha.
Monday, March 26, 2012
Fetching data from a Flat file in SQL Server2000
Posted - 11/03/2006 : 08:00:37 AM
----
Hi,
I am new to SQL Server 2000.
I have to write a stored procedure that fetches some of the fields from
two flat files.
Here is my requirement...
There is a flat file by name "File1", Here i have to Fetch all the
Document ids (first 20 characters) from this file.
Then join this file using trim(Document id) with the below file's
Document id (column 17 to 28) to get the following fields:
"File2" - fetch the following fields (by position)
Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55)
Join the above result set with the following
condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
following fields:
(where result set is the result obtained by joining the flat files)
Sent (loistatus.loisentdate)
Unit (eligibilityorg.univid)
where Enrollkeys, loistatus, eligibilityorg are the tables in the
datase..
Actually I solved this issue using DTS package, but my DBA is not
allowing me to use DTS package.
He is telling me to Try using Stored Procedure to access the flat files
and getting the values.
And also I should not Use any execute or DDL statement inside the
Stored procedure.
So how can i proceed..
Please reply ASAP..One solution is that you can use openrowset for accessing directly the flat
file in your select query itself.
Amarnath
"Praveen" wrote:
> Posted - 11/03/2006 : 08:00:37 AM
>
> ----
> Hi,
> I am new to SQL Server 2000.
> I have to write a stored procedure that fetches some of the fields from
> two flat files.
> Here is my requirement...
> There is a flat file by name "File1", Here i have to Fetch all the
> Document ids (first 20 characters) from this file.
> Then join this file using trim(Document id) with the below file's
> Document id (column 17 to 28) to get the following fields:
> "File2" - fetch the following fields (by position)
> Memberid (column 899 to 908)
> Received Date (column 35 to 42)
> Verifier (column 51 to 55)
> Join the above result set with the following
> condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
> following fields:
> (where result set is the result obtained by joining the flat files)
> Sent (loistatus.loisentdate)
> Unit (eligibilityorg.univid)
> where Enrollkeys, loistatus, eligibilityorg are the tables in the
> datase..
> Actually I solved this issue using DTS package, but my DBA is not
> allowing me to use DTS package.
> He is telling me to Try using Stored Procedure to access the flat files
> and getting the values.
> And also I should not Use any execute or DDL statement inside the
> Stored procedure.
> So how can i proceed..
> Please reply ASAP..
>
----
Hi,
I am new to SQL Server 2000.
I have to write a stored procedure that fetches some of the fields from
two flat files.
Here is my requirement...
There is a flat file by name "File1", Here i have to Fetch all the
Document ids (first 20 characters) from this file.
Then join this file using trim(Document id) with the below file's
Document id (column 17 to 28) to get the following fields:
"File2" - fetch the following fields (by position)
Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55)
Join the above result set with the following
condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
following fields:
(where result set is the result obtained by joining the flat files)
Sent (loistatus.loisentdate)
Unit (eligibilityorg.univid)
where Enrollkeys, loistatus, eligibilityorg are the tables in the
datase..
Actually I solved this issue using DTS package, but my DBA is not
allowing me to use DTS package.
He is telling me to Try using Stored Procedure to access the flat files
and getting the values.
And also I should not Use any execute or DDL statement inside the
Stored procedure.
So how can i proceed..
Please reply ASAP..One solution is that you can use openrowset for accessing directly the flat
file in your select query itself.
Amarnath
"Praveen" wrote:
> Posted - 11/03/2006 : 08:00:37 AM
>
> ----
> Hi,
> I am new to SQL Server 2000.
> I have to write a stored procedure that fetches some of the fields from
> two flat files.
> Here is my requirement...
> There is a flat file by name "File1", Here i have to Fetch all the
> Document ids (first 20 characters) from this file.
> Then join this file using trim(Document id) with the below file's
> Document id (column 17 to 28) to get the following fields:
> "File2" - fetch the following fields (by position)
> Memberid (column 899 to 908)
> Received Date (column 35 to 42)
> Verifier (column 51 to 55)
> Join the above result set with the following
> condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
> following fields:
> (where result set is the result obtained by joining the flat files)
> Sent (loistatus.loisentdate)
> Unit (eligibilityorg.univid)
> where Enrollkeys, loistatus, eligibilityorg are the tables in the
> datase..
> Actually I solved this issue using DTS package, but my DBA is not
> allowing me to use DTS package.
> He is telling me to Try using Stored Procedure to access the flat files
> and getting the values.
> And also I should not Use any execute or DDL statement inside the
> Stored procedure.
> So how can i proceed..
> Please reply ASAP..
>
Friday, March 23, 2012
feature?
I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restor
ed
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok an
d
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I go
t
> at the end told me that DB1 had been restored...when in fact it was DB2 th
at
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restor
ed
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok an
d
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I go
t
> at the end told me that DB1 had been restored...when in fact it was DB2 th
at
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
feature?
I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
Andre
Andre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
Andre
Andre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
feature?
I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Wednesday, March 21, 2012
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Hi,
I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
expand a data file by 4 GB - it does not report any error. The windows
explorer shows the new size. But the enterpise manager and sp_helpdb show the
old size and there is an entry in the SQL error log that says..
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Has any one come across this before? What is the solution?
Sounds like you have some serious issues with your disk. 27 is an OS error, "Cannot find requested
sector". Is the file compressed? If so, this isn't supported, and things like these are expected
(although I'm not sure whether this particular error message is expected). If not, I'd run a
comprehensive check at the file system/disk level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:0AD05B12-0C6F-4A1E-9B73-C39BB1BDD360@.microsoft.com...
> Hi,
> I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
> expand a data file by 4 GB - it does not report any error. The windows
> explorer shows the new size. But the enterpise manager and sp_helpdb show the
> old size and there is an entry in the SQL error log that says..
> fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
> Has any one come across this before? What is the solution?
I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
expand a data file by 4 GB - it does not report any error. The windows
explorer shows the new size. But the enterpise manager and sp_helpdb show the
old size and there is an entry in the SQL error log that says..
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Has any one come across this before? What is the solution?
Sounds like you have some serious issues with your disk. 27 is an OS error, "Cannot find requested
sector". Is the file compressed? If so, this isn't supported, and things like these are expected
(although I'm not sure whether this particular error message is expected). If not, I'd run a
comprehensive check at the file system/disk level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:0AD05B12-0C6F-4A1E-9B73-C39BB1BDD360@.microsoft.com...
> Hi,
> I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
> expand a data file by 4 GB - it does not report any error. The windows
> explorer shows the new size. But the enterpise manager and sp_helpdb show the
> old size and there is an entry in the SQL error log that says..
> fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
> Has any one come across this before? What is the solution?
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Hi,
I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
expand a data file by 4 GB - it does not report any error. The windows
explorer shows the new size. But the enterpise manager and sp_helpdb show th
e
old size and there is an entry in the SQL error log that says..
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Has any one come across this before? What is the solution?Sounds like you have some serious issues with your disk. 27 is an OS error,
"Cannot find requested
sector". Is the file compressed? If so, this isn't supported, and things lik
e these are expected
(although I'm not sure whether this particular error message is expected). I
f not, I'd run a
comprehensive check at the file system/disk level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:0AD05B12-0C6F-4A1E-9B73-C39BB1BDD360@.microsoft.com...
> Hi,
> I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try
to
> expand a data file by 4 GB - it does not report any error. The windows
> explorer shows the new size. But the enterpise manager and sp_helpdb show
the
> old size and there is an entry in the SQL error log that says..
> fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
> Has any one come across this before? What is the solution?sql
I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
expand a data file by 4 GB - it does not report any error. The windows
explorer shows the new size. But the enterpise manager and sp_helpdb show th
e
old size and there is an entry in the SQL error log that says..
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Has any one come across this before? What is the solution?Sounds like you have some serious issues with your disk. 27 is an OS error,
"Cannot find requested
sector". Is the file compressed? If so, this isn't supported, and things lik
e these are expected
(although I'm not sure whether this particular error message is expected). I
f not, I'd run a
comprehensive check at the file system/disk level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:0AD05B12-0C6F-4A1E-9B73-C39BB1BDD360@.microsoft.com...
> Hi,
> I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try
to
> expand a data file by 4 GB - it does not report any error. The windows
> explorer shows the new size. But the enterpise manager and sp_helpdb show
the
> old size and there is an entry in the SQL error log that says..
> fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
> Has any one come across this before? What is the solution?sql
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Hi,
I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
expand a data file by 4 GB - it does not report any error. The windows
explorer shows the new size. But the enterpise manager and sp_helpdb show the
old size and there is an entry in the SQL error log that says..
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Has any one come across this before? What is the solution?Sounds like you have some serious issues with your disk. 27 is an OS error, "Cannot find requested
sector". Is the file compressed? If so, this isn't supported, and things like these are expected
(although I'm not sure whether this particular error message is expected). If not, I'd run a
comprehensive check at the file system/disk level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:0AD05B12-0C6F-4A1E-9B73-C39BB1BDD360@.microsoft.com...
> Hi,
> I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
> expand a data file by 4 GB - it does not report any error. The windows
> explorer shows the new size. But the enterpise manager and sp_helpdb show the
> old size and there is an entry in the SQL error log that says..
> fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
> Has any one come across this before? What is the solution?
I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
expand a data file by 4 GB - it does not report any error. The windows
explorer shows the new size. But the enterpise manager and sp_helpdb show the
old size and there is an entry in the SQL error log that says..
fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
Has any one come across this before? What is the solution?Sounds like you have some serious issues with your disk. 27 is an OS error, "Cannot find requested
sector". Is the file compressed? If so, this isn't supported, and things like these are expected
(although I'm not sure whether this particular error message is expected). If not, I'd run a
comprehensive check at the file system/disk level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:0AD05B12-0C6F-4A1E-9B73-C39BB1BDD360@.microsoft.com...
> Hi,
> I have a server that is running MSSQL2000 SP4 + 2159 hotfix. Whever I try to
> expand a data file by 4 GB - it does not report any error. The windows
> explorer shows the new size. But the enterpise manager and sp_helpdb show the
> old size and there is an entry in the SQL error log that says..
> fcb::ZeroFile(): GetOverLappedResult() failed with error 27.
> Has any one come across this before? What is the solution?
Friday, March 9, 2012
Fastest method for Inserting 1 million records into SQL Database
I am reading a text file and modifing the data to match fields in a SQL 2000 Database then inserting the record in. I am using vb.net and have tried various methods but all are to slow. I would appreciate any help anybody could offer.
Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.
Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.
Fastest method for Inserting 1 million records into SQL Database
Adam is totally correct, however you get best results by
putting the file on the Server that your SQL Server is
on, that way there isn't going to be a network overhead.
J
>--Original Message--
>I am reading a text file and modifing the data to match
fields in a SQL 2000 Database then inserting the record
in. I am using vb.net and have tried various methods but
all are to slow. I would appreciate any help anybody
could offer.
>.
>
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2d85901c469da$da1d8050$a301280a@.phx.gbl...
> ..and I hope people don't take that as arrogantly as it
> sounds...
The part about network overhead? Yeah, I thought you went way over the
line on that one
Actually, I have no clue what you're talking about!
putting the file on the Server that your SQL Server is
on, that way there isn't going to be a network overhead.
J
>--Original Message--
>I am reading a text file and modifing the data to match
fields in a SQL 2000 Database then inserting the record
in. I am using vb.net and have tried various methods but
all are to slow. I would appreciate any help anybody
could offer.
>.
>
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2d85901c469da$da1d8050$a301280a@.phx.gbl...
> ..and I hope people don't take that as arrogantly as it
> sounds...
The part about network overhead? Yeah, I thought you went way over the
line on that one
Actually, I have no clue what you're talking about!
Fastest method for Inserting 1 million records into SQL Database
Adam is totally correct, however you get best results by
putting the file on the Server that your SQL Server is
on, that way there isn't going to be a network overhead.
J
>--Original Message--
>I am reading a text file and modifing the data to match
fields in a SQL 2000 Database then inserting the record
in. I am using vb.net and have tried various methods but
all are to slow. I would appreciate any help anybody
could offer.
>.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2d85901c469da$da1d8050$a301280a@.phx
.gbl...
> ..and I hope people don't take that as arrogantly as it
> sounds...
The part about network overhead? Yeah, I thought you went way over the
line on that one
Actually, I have no clue what you're talking about!
putting the file on the Server that your SQL Server is
on, that way there isn't going to be a network overhead.
J
>--Original Message--
>I am reading a text file and modifing the data to match
fields in a SQL 2000 Database then inserting the record
in. I am using vb.net and have tried various methods but
all are to slow. I would appreciate any help anybody
could offer.
>.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2d85901c469da$da1d8050$a301280a@.phx
.gbl...
> ..and I hope people don't take that as arrogantly as it
> sounds...
The part about network overhead? Yeah, I thought you went way over the
line on that one
Actually, I have no clue what you're talking about!
Fastest method for Inserting 1 million records into SQL Database
I am reading a text file and modifing the data to match fields in a SQL 2000
Database then inserting the record in. I am using vb.net and have tried var
ious methods but all are to slow. I would appreciate any help anybody could
offer.Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.
Database then inserting the record in. I am using vb.net and have tried var
ious methods but all are to slow. I would appreciate any help anybody could
offer.Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.
Wednesday, March 7, 2012
fast query execution
i have a query which is taking 15 minutes to execute.The databse is very
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutesHi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kbs.Lemma.LemmaModificationDate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId =kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )and
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1>) AS l, (<subquery2>)
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutesHi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kbs.Lemma.LemmaModificationDate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId =kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )and
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1>) AS l, (<subquery2>)
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString
fast query execution
i have a query which is taking 15 minutes to execute.The databse is very
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutes
Hi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes
|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName,
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kb s.Lemma.LemmaModificationDate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =
kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId =
kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )and
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =
kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards
|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1>) AS l, (<subquery2>)
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutes
Hi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes
|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName,
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kb s.Lemma.LemmaModificationDate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =
kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId =
kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )and
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =
kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards
|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1>) AS l, (<subquery2>)
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString
fast query execution
i have a query which is taking 15 minutes to execute.The databse is very
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutesHi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName,
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kbs.Lemma.LemmaModificationD
ate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =
kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId
=
kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )an
d
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeI
d
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =
kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1> ) AS l, (<subquery2> )
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutesHi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName,
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kbs.Lemma.LemmaModificationD
ate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =
kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId
=
kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )an
d
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeI
d
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =
kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1> ) AS l, (<subquery2> )
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString
Friday, February 24, 2012
Failure writing file
I get "Failure writing file" ... when my subscription runs? Does any one know
how to fix it?Can you post the error message in the ReportServerService<date>.log file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"AbiBaby" <AbiBaby@.discussions.microsoft.com> wrote in message
news:17C09CC5-47F8-447A-B185-34143ABDAE2C@.microsoft.com...
> I get "Failure writing file" ... when my subscription runs? Does any one
know
> how to fix it?|||you should give to you network user the right to access (write) the folder
where you report are delivred.
"AbiBaby" wrote:
> I get "Failure writing file" ... when my subscription runs? Does any one know
> how to fix it?|||It has all Rights, i eneabled everyone. I wrote my own subscription service.
And it works!
"Tino [Securitas.fr]" wrote:
> you should give to you network user the right to access (write) the folder
> where you report are delivred.
> "AbiBaby" wrote:
> > I get "Failure writing file" ... when my subscription runs? Does any one know
> > how to fix it?
how to fix it?Can you post the error message in the ReportServerService<date>.log file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"AbiBaby" <AbiBaby@.discussions.microsoft.com> wrote in message
news:17C09CC5-47F8-447A-B185-34143ABDAE2C@.microsoft.com...
> I get "Failure writing file" ... when my subscription runs? Does any one
know
> how to fix it?|||you should give to you network user the right to access (write) the folder
where you report are delivred.
"AbiBaby" wrote:
> I get "Failure writing file" ... when my subscription runs? Does any one know
> how to fix it?|||It has all Rights, i eneabled everyone. I wrote my own subscription service.
And it works!
"Tino [Securitas.fr]" wrote:
> you should give to you network user the right to access (write) the folder
> where you report are delivred.
> "AbiBaby" wrote:
> > I get "Failure writing file" ... when my subscription runs? Does any one know
> > how to fix it?
Failure Workflow Does Not Fire
Hello,
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.
I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.
For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.
What do I need to do so that the failure workflow occurs when the step fails?
Thank you for your help!
cdun2Are you sure the fact that no file is there will cause the step to error out?|||u need to trap errors from external programs like bat files to have the failed workflow activated. aslo not all external programs returns error code to the calling application. for bat files, u will have to set errorlevel to make the calling program understand the success/failure
for example when using xp_cmdshell, this will call the failure workflow, if present
declare @.err int
exec @.err = master..xp_cmdshell 'C:\xx.bat'
if @.err = 1
RAISERROR ('err',16,1)
the above code will not fire the failure workflow if u just execute
exec master..xp_cmdshell 'C:\xx.bat'
and even if the xx.bat is not present in folder C:\|||Thank you for your help!
cdun2|||Another option is to create an operator when you scheduled the job and an e-mail will be sent out if the job fails.
Good luck
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.
I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.
For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.
What do I need to do so that the failure workflow occurs when the step fails?
Thank you for your help!
cdun2Are you sure the fact that no file is there will cause the step to error out?|||u need to trap errors from external programs like bat files to have the failed workflow activated. aslo not all external programs returns error code to the calling application. for bat files, u will have to set errorlevel to make the calling program understand the success/failure
for example when using xp_cmdshell, this will call the failure workflow, if present
declare @.err int
exec @.err = master..xp_cmdshell 'C:\xx.bat'
if @.err = 1
RAISERROR ('err',16,1)
the above code will not fire the failure workflow if u just execute
exec master..xp_cmdshell 'C:\xx.bat'
and even if the xx.bat is not present in folder C:\|||Thank you for your help!
cdun2|||Another option is to create an operator when you scheduled the job and an e-mail will be sent out if the job fails.
Good luck
Subscribe to:
Posts (Atom)