Integrated Security: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

Intermittently Slow query - Left Join

Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:

Descriptions table:

ID | Title
Doc1 | Document 1
Doc2 | Document 2

Documents table:

ID | Parent
Doc1 | 10400
Doc2 | 10400
Doc1 | 20189
Doc3 | 20189

View:

ID | Parent | Description
Doc1 | 10400 | Document 1
Doc2 | 10400 | Document 2
Doc1 | 20189 | Document 1
Doc3 | 20189 | (null)

So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.

In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.

It's running on SQL Server 2005 with 2GB of RAM.

Any suggestions on tracking down the reason for the slowness would be great.

Thanks in advance!!!

-DanPost the query you are executing.|||SELECT
dbo.Table1.ItemType,
dbo.Table1.ItemLabel,
dbo.Table1.ItemParent,
dbo.Table1.ItemID,
dbo.Table1.ProjectID,
dbo.Table1.ItemDate,
dbo.Table1.Active,
dbo.Table1.ItemBaseLabel,
dbo.Table2.ItemDescription
FROM dbo.Table1 LEFT OUTER JOIN dbo.Table2
ON dbo.Table1.ProjectID = dbo.Table2.ProjectID
AND dbo.Table1.ItemBaseLabel = dbo.Table2.ItemBaseLabel

Here you go!

-Dan|||where are the indices placed? have you looked at the execution plan?|||That's weird... I just checked the indices on the base table and there are three. One is By ItemID. Another is by ItemLabel and another is by ItemLabel, ItemParent, Item ID, and another field. Could they be throwing off the way the data is being stored?

There are no indices on the view and I cannot add one. It comes up with an error saying that it can't add an index because it's not schema based. Please, bear with me - I'm new to 2005. This was originally stored on MSSQL 7.0 and it worked fine there.

Thanks!

-Dan|||typically you want indices on search conditions, primary keys (there is usually a clustered one there) and the foriegn keys but you should examine the execution plan first to make sure this will help. if they are already there, make sure they are not fragmented, the statistics are up to date, and then if this is a sp, recompile it. I am not working with 2K5 just yet.|||I checked the indices and everything seems to be in order. I also tried it through a stored procedure and it still takes way too long.

Any other ideas?

-Dan|||for the third time... have you looked at the execution plan?|||Sorry. The execution plan said that a table scan on the dbo.Table2 was 100% cost. So I looked at the fields it was referencing and added a column to the Table2 index and then re-ran the execution plan and it was more divided among the tasks, now with 2 index scans.

Went back to the query and now it's blazing fast.

Apparently my listening skills are about as good as my databasing skills...

Thanks Thrasymachus. :)

-Dan

intermittent stalling on a query

I've been trying to diagonose a strange problem with an application that I
inherited. Every few days, SQL Server will take between 3 to 4 minutes to
perform a really simple update.
The application that runs against the database is a real-time monitoring
program that communicates to a number of devices. It probably issues about
a dozen queries every second, 80% updates, 10% selects, 10% inserts. There
are no deletes being done. For the most part, all of the queries run in
less than a second. In the application, the CommandTimeout value on the
queries is set to 2 minutes. The application is setup to restart itself on
when a timeout occurs. And since the CommandTimeout is not high enough (2
mins vs 3+ mins), the application will into a non-stop cycle of restarting
itself... Until I go into Query Analyzer, and run a simple update statement
against an arbitrary table; which will take between 3 to 4 minutes to
complete, and then afterwards, the application will start running fine
again. I've seen instances where the application would stay in a restart
cycle for days at a time.
There does not appear to be a deadlock or open transaction. I've tried
running SP_WHO2, SP_LOCK, DBCC OPENTRAN, DBCC CHECKDB and didn't see any
issues.
The application is running on the same computer as the SQL server. It is
the sole application running against the database. The application uses the
SQLOLEDB provider.
The database is about 10 MB, the transaction log is 3 GB (mostly empty).
Server properties: Windows Server 2003 Standard Edition, Pentium 4 2.8 GHz
(hyperthreading-enabled), 512 MB RAM, and plenty of free drive space.
SQL Server settings:
- SQL Server 2000 8.00.818 (SP3)
- Memory is setup for dynamic allocation
- Processor is setup to use the two "instances"
- "Implicit transactions" is checked
- "Close cursor on COMMIT" is checked
- "Auto Grow" is enabled for both the data file and transaction log
- "Auto Update Statistics", "Auto Create Statistics", and "Torn Page
Detection" are checked
There are jobs to backup both the data file and transaction log.
I noticed that the timeouts tend to be from one of two queries (but about
99% of the time, it's one of these queries being called). Each of the
queries is literally a single update statement touching a single table,
wrapped in a stored procedure. And the tables that they update have at most
a dozen records in them.
Things, I've tried,
- I thought it was auto grow causing the stall, but I manually increased the
database size, and the application did not hiccup.
- I also thought it might be statistics related, so I ran a "sp_updatestats"
against the database. The next day, the problem came up again.
- I've tried adding "with recompile" to the stored procedure with no luck
either.
This seems like it might be server configuration issue, but I'm not sure
what else to check.
TYIA for any advice.My bet is still on the Autogrow. First off I assume you mean the db is 10GB
and not MB? If autogrow kicks in it will attempt to grow the file by about
1GB. On a slow disk subsystem this can take a while. If the connection
that issued the command that forced the autogrow times out the growth will
be canceled and you are back to where you started. The next insert or
update may force another growth which may or may not succeed. It is all
about timing and resources. When you manually grow the DB there is no
problem because there is not a statement (insert or update) in the process
of executing that forced the growth so it will succeed. You should always
have plenty of free space in the db and autogrow should never kick in. When
you run low you need to manually grow it at the appropriate time so you
always have enough free space. If you are that low you are too low anyway
since the first time you do a reindex you will need more space. If you
have auto shrink turned on then turn it off immediately. You should also
change the growth to be a fixed amount vs. a percentage. Make it an amount
that can grow in no more than 30 seconds or so. That way you won't get a
timeout if it does kick in.
Andrew J. Kelly SQL MVP
"John Smith" <john@.smith.com> wrote in message
news:OtaoqmFIFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I've been trying to diagonose a strange problem with an application that I
> inherited. Every few days, SQL Server will take between 3 to 4 minutes to
> perform a really simple update.
> The application that runs against the database is a real-time monitoring
> program that communicates to a number of devices. It probably issues
> about a dozen queries every second, 80% updates, 10% selects, 10% inserts.
> There are no deletes being done. For the most part, all of the queries
> run in less than a second. In the application, the CommandTimeout value
> on the queries is set to 2 minutes. The application is setup to restart
> itself on when a timeout occurs. And since the CommandTimeout is not high
> enough (2 mins vs 3+ mins), the application will into a non-stop cycle of
> restarting itself... Until I go into Query Analyzer, and run a simple
> update statement against an arbitrary table; which will take between 3 to
> 4 minutes to complete, and then afterwards, the application will start
> running fine again. I've seen instances where the application would stay
> in a restart cycle for days at a time.
> There does not appear to be a deadlock or open transaction. I've tried
> running SP_WHO2, SP_LOCK, DBCC OPENTRAN, DBCC CHECKDB and didn't see any
> issues.
> The application is running on the same computer as the SQL server. It is
> the sole application running against the database. The application uses
> the SQLOLEDB provider.
> The database is about 10 MB, the transaction log is 3 GB (mostly empty).
> Server properties: Windows Server 2003 Standard Edition, Pentium 4 2.8 GHz
> (hyperthreading-enabled), 512 MB RAM, and plenty of free drive space.
> SQL Server settings:
> - SQL Server 2000 8.00.818 (SP3)
> - Memory is setup for dynamic allocation
> - Processor is setup to use the two "instances"
> - "Implicit transactions" is checked
> - "Close cursor on COMMIT" is checked
> - "Auto Grow" is enabled for both the data file and transaction log
> - "Auto Update Statistics", "Auto Create Statistics", and "Torn Page
> Detection" are checked
> There are jobs to backup both the data file and transaction log.
> I noticed that the timeouts tend to be from one of two queries (but about
> 99% of the time, it's one of these queries being called). Each of the
> queries is literally a single update statement touching a single table,
> wrapped in a stored procedure. And the tables that they update have at
> most a dozen records in them.
> Things, I've tried,
> - I thought it was auto grow causing the stall, but I manually increased
> the database size, and the application did not hiccup.
> - I also thought it might be statistics related, so I ran a
> "sp_updatestats" against the database. The next day, the problem came up
> again.
> - I've tried adding "with recompile" to the stored procedure with no luck
> either.
> This seems like it might be server configuration issue, but I'm not sure
> what else to check.
> TYIA for any advice.
>|||Thanks, I didn't realize the part about the autogrow being canceled on a
timeout.
I checked again, and was off a bit. It's at 30 MB, but still tiny. It
might have been the transaction log that was expanding (is it affected the
same way, by a connection timeout?).
I'll keep a closer eye on this then. Thanks again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eRL2YkGIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> My bet is still on the Autogrow. First off I assume you mean the db is
> 10GB and not MB? If autogrow kicks in it will attempt to grow the file by
> about 1GB. On a slow disk subsystem this can take a while. If the
> connection that issued the command that forced the autogrow times out the
> growth will be canceled and you are back to where you started. The next
> insert or update may force another growth which may or may not succeed. It
> is all about timing and resources. When you manually grow the DB there is
> no problem because there is not a statement (insert or update) in the
> process of executing that forced the growth so it will succeed. You
> should always have plenty of free space in the db and autogrow should
> never kick in. When you run low you need to manually grow it at the
> appropriate time so you always have enough free space. If you are that
> low you are too low anyway since the first time you do a reindex you will
> need more space. If you have auto shrink turned on then turn it off
> immediately. You should also change the growth to be a fixed amount vs. a
> percentage. Make it an amount that can grow in no more than 30 seconds or
> so. That way you won't get a timeout if it does kick in.
> --
> Andrew J. Kelly SQL MVP
>
> "John Smith" <john@.smith.com> wrote in message
> news:OtaoqmFIFHA.2356@.TK2MSFTNGP12.phx.gbl...
>|||Yes the tran log is essentially the same behavior in that any statements
that require logging will sit and wait until the log is finished expanding.
If you have a 30MB db and a 3GB log you are not doing something right. You
stated that you are doing regular Full and Log backups. If that were the
case and you didn't have any long running open transactions your log file
should only be a few hundred MB at best. Try running DBCC OPENTRAN and see
what it tells you. Also check your backups to make sure they are actually
happening with no errors.
Andrew J. Kelly SQL MVP
"John Smith" <john@.smith.com> wrote in message
news:OX4$t1IIFHA.608@.TK2MSFTNGP10.phx.gbl...
> Thanks, I didn't realize the part about the autogrow being canceled on a
> timeout.
> I checked again, and was off a bit. It's at 30 MB, but still tiny. It
> might have been the transaction log that was expanding (is it affected the
> same way, by a connection timeout?).
> I'll keep a closer eye on this then. Thanks again.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eRL2YkGIFHA.3336@.TK2MSFTNGP10.phx.gbl...
>

Wednesday, March 28, 2012

Intermittent slow SQL2005 database

I'm querying a small SQL2005 database and finding that the query can sometimes complete in under a second and then 5 minutes later the same query can take 15 minutes to complete.

The query I'm running is very simple as follows:
select TOP 26 * from vSearchListOpportunityItem WHERE OpIt_OpportunityId=2495 ORDER BY Prod_Name, OpIt_OpportunityItemId

The view it is pulling data from only contains only 1890 lines, which in turn pulls data from 3 tables with 821, 2560, and 1957 lines of data. In other words it's small. I have noticed that if I try and open the smallest of these tables while on a 'go slow' period it also takes around 15 minutes to return the data.

The database was originally on SQL 2000. It is the only database on this powerful quad core server.
The SQL Server CPU usage never goes above 40%, and always has free memory.
No sign of locks.

I can't figure out why such a small database is going so slow with such a simple query. Any ideas?

Depends on the view definition, one mistake in the view definition and the results is a cartesian product resulting in e.g. 821x2560x1957 (or any other variation) of the data leading to a non-wanted data explosion. If opening one table also slows down the process, make sure that there is no *sophisticated* computed column on the table which could slow down the operation.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Perhaps this 'secret' dba trick will help.

Use the following database option:

SET OPTION RUN_FASTER ON

And then, I have a bridge to sell if you are interested...

Wink

(Please post your query and the VIEW definition, as well as the unlaying table DDL, and we 'might' be able to give you more directed guidance.)

|||

> SET OPTION RUN_FASTER ON

No I tried that already.

Here is the View:

USE [CRM]

/****** Object: View [dbo].[vSearchListOpportunityItem] Script Date: 08/03/2007 09:41:00 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[vSearchListOpportunityItem] AS SELECT Prod_ProductId, Prod_Name, Prod_Description, Prod_ListPrice, Prod_ListPrice_CID, OpportunityItem.*,Opportunity.* FROM Opportunity,OpportunityItem LEFT OUTER JOIN Products ON OpIt_ProductId = Prod_ProductId WHERE OpIt_OpportunityId=Oppo_OpportunityId and OpIt_Deleted IS NULL

And the table Products (this is the table that also runs slow when returning its 821 rows):

USE [CRM]

/****** Object: Table [dbo].[Products] Script Date: 08/03/2007 09:43:20 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Products](

[Prod_ProductId] [int] NOT NULL,

[Prod_Name] [nchar](64) NULL,

[Prod_Description] [nchar](60) NULL,

[Prod_CreatedBy] [int] NULL,

[Prod_CreatedDate] [datetime] NULL,

[Prod_UpdatedBy] [int] NULL,

[Prod_UpdatedDate] [datetime] NULL,

[Prod_Deleted] [tinyint] NULL,

[Prod_ListPrice] [numeric](24, 6) NULL,

[Prod_TimeStamp] [datetime] NULL,

[Prod_SegmentID] [int] NULL,

[Prod_ChannelID] [int] NULL,

[Prod_ListPrice_CID] [int] NULL,

[Prod_ICITEM] [nchar](24) NULL,

[Prod_ICType] [nchar](10) NULL,

[prod_nsrcategory] [nchar](40) NULL,

[prod_nsrtype] [nchar](40) NULL

) ON [PRIMARY]

Table Opportunity 2561 rows:

USE [CRM]

/****** Object: Table [dbo].[Opportunity] Script Date: 08/03/2007 09:43:55 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Opportunity](

[Oppo_OpportunityId] [int] NOT NULL,

[Oppo_PrimaryCompanyId] [int] NULL,

[Oppo_PrimaryPersonId] [int] NULL,

[Oppo_AssignedUserId] [int] NULL,

[Oppo_ChannelId] [int] NULL,

[Oppo_Description] [nchar](40) NULL,

[Oppo_Type] [nchar](40) NULL,

[Oppo_Product] [nchar](30) NULL,

[Oppo_Source] [nchar](40) NULL,

[Oppo_Note] [ntext] NULL,

[Oppo_CustomerRef] [nchar](30) NULL,

[Oppo_Opened] [datetime] NULL,

[Oppo_Closed] [datetime] NULL,

[Oppo_Status] [nchar](40) NULL,

[Oppo_Stage] [nchar](40) NULL,

[Oppo_Forecast] [numeric](24, 6) NULL,

[Oppo_Certainty] [int] NULL,

[Oppo_Priority] [nchar](40) NULL,

[Oppo_TargetClose] [datetime] NULL,

[Oppo_CreatedBy] [int] NULL,

[Oppo_CreatedDate] [datetime] NULL,

[Oppo_UpdatedBy] [int] NULL,

[Oppo_UpdatedDate] [datetime] NULL,

[Oppo_TimeStamp] [datetime] NULL,

[Oppo_Deleted] [tinyint] NULL,

[Oppo_Total] [numeric](24, 6) NULL,

[Oppo_NotifyTime] [datetime] NULL,

[Oppo_SMSSent] [nchar](20) NULL,

[Oppo_WaveItemId] [int] NULL,

[Oppo_SegmentID] [int] NULL,

[Oppo_SecTerr] [int] NULL,

[Oppo_WorkflowId] [int] NULL,

[Oppo_LeadID] [int] NULL,

[Oppo_Forecast_CID] [int] NULL,

[Oppo_Total_CID] [int] NULL,

[oppo_scenario] [nchar](40) NULL,

[oppo_decisiontimeframe] [nchar](40) NULL,

[oppo_Currency] [int] NULL,

[oppo_TotalOrders_CID] [int] NULL,

[oppo_TotalOrders] [numeric](24, 6) NULL,

[oppo_totalQuotes_CID] [int] NULL,

[oppo_totalQuotes] [numeric](24, 6) NULL,

[oppo_NoDiscAmtSum] [numeric](24, 6) NULL,

[oppo_NoDiscAmtSum_CID] [int] NULL,

[Oppo_OrderNumber] [nchar](100) NULL,

[oppo_CurrentSystem] [nchar](15) NULL,

[oppo_ProductInterest] [nchar](40) NULL,

[oppo_deposit_received] [nchar](1) NULL,

[oppo_Install_Complete] [nchar](1) NULL,

[oppo_NewExisting] [nchar](40) NULL,

[oppo_InstallDate] [datetime] NULL,

[oppo_invoicenumber] [nchar](20) NULL,

[oppo_Soldby] [int] NULL,

[oppo_SaleDate] [datetime] NULL,

[oppo_Creditnoteno] [nchar](10) NULL,

[oppo_creditnoteamount] [numeric](24, 6) NULL,

[oppo_creditnoteamount_CID] [int] NULL,

[oppo_invoiceamount] [numeric](24, 6) NULL,

[oppo_invoiceamount_CID] [int] NULL,

[oppo_invoicedate] [datetime] NULL,

[oppo_Depositamoun] [numeric](24, 6) NULL,

[oppo_Depositamoun_CID] [int] NULL,

[oppo_SuppThisSale] [numeric](24, 6) NULL,

[oppo_SuppThisSale_CID] [int] NULL,

[oppo_SuppFreq] [nchar](40) NULL,

[oppo_SupportInAccpac] [nchar](1) NULL,

[oppo_SupportAddedBy] [int] NULL,

[oppo_supportAddedDate] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table OpportunityItem 1957 rows:

USE [CRM]
GO
/****** Object: Table [dbo].[OpportunityItem] Script Date: 08/03/2007 09:44:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OpportunityItem](
[OpIt_OpportunityItemId] [int] NOT NULL,
[OpIt_OpportunityId] [int] NOT NULL,
[OpIt_ProductId] [int] NOT NULL,
[OpIt_Quantity] [numeric](24, 6) NULL,
[OpIt_OriginalListPrice] [numeric](24, 6) NULL,
[OpIt_QuotedPrice] [numeric](24, 6) NULL,
[OpIt_QuotedPriceTotal] [numeric](24, 6) NULL,
[OpIt_Discount] [numeric](24, 6) NULL,
[OpIt_CreatedBy] [int] NULL,
[OpIt_CreatedDate] [datetime] NULL,
[OpIt_UpdatedBy] [int] NULL,
[OpIt_UpdatedDate] [datetime] NULL,
[OpIt_TimeStamp] [datetime] NULL,
[OpIt_Deleted] [tinyint] NULL,
[OpIt_SegmentID] [int] NULL,
[OpIt_ChannelID] [int] NULL,
[OpIt_Discount_CID] [int] NULL,
[OpIt_OriginalListPrice_CID] [int] NULL,
[OpIt_QuotedPrice_CID] [int] NULL,
[OpIt_QuotedPriceTotal_CID] [int] NULL,
[OpIt_Ordered] [nchar](1) NULL
) ON [PRIMARY]

|||

> SET OPTION RUN_FASTER ON

No I tried that already.

Since that didn't work for you, how about trying a RESTORE with the 'secret' DBA RESTORE option:

RESTORE DATEBASE MyDatabase

WITH FAST_QUERY_MODE_DWIM

*( DWIM = Do What I Mean )

I'll offer the following comments, some of which 'may' help with this issue.

First, change your datatype for Oppo_Note to varchar(max). Text, ntext, and image are on the deprecation list.

Second, do you really need ALL columns from Opportunity and OpportunityItem? -You are retrieving approximately 94 columns per row, and that is consuming a lot of memory in order to create the resultset. From a cursory look, it appears that each row needs a couple thousand bytes.

Then I would -re-organize the VIEW a bit to optimize the JOIN. (There is a chance that your VIEW is doing a cartesian JOIN between Opportunity and OpportunityItem (the resultset is over 5 million rows -check the execution plan. A cartesian JOIN would require in excess of 10GB just to hold the intermediate JOIN resultset.) The bottom line is that there is a lot of I/O happening here, perhaps some of which is unnecessary.

ALTER VIEW [dbo].[vSearchListOpportunityItem]
AS
SELECT
p.Prod_ProductId,
p.Prod_Name,
p.Prod_Description,
p.Prod_ListPrice,
p.Prod_ListPrice_CID,
o.*, --List Actual Columns Needed
oi.* --List Actual Columns Needed
FROM Opportunity o,
JOIN OpportunityItem oi
ON ( o.OpIt_OpportunityId = oi.Oppo_OpportunityId
AND o.OpIt_Deleted IS NULL
)
LEFT OUTER JOIN Products p
ON o.OpIt_ProductId = p.Prod_ProductId

Then, verify the indexing

Opportunity(OpIt_OpportunityId),

OpportunityItem(Oppo_OpportunityId)

OpportunityItem(OpIt_ProductId)

Products(Prod_ProductId)

You may also find value is using the Database Tuning Wizard to determine if additional indexing can be useful for this operation.

And lastly, the naming convention used is 'awkward' to be charitable. Prefixing a column name with the table indicator is quite 'out of favor'. You are wasting keystrokes EVERY time you refer to a column. Anytime a JOIN occurs, you 'should' be using an table ALIAS (as above, [ o, oi, p ] -and it actually makes the code more readible. A good 'rule of thumb' is that if the keystroke does not add value, it is wasted. With the column names above, [Prod_], [OpIt_], [Oppo_] are wasted keystokes, they don't add any value to distinguish the columns. The table alias clearly identifies where the column comes from AND also clarifies the JOIN conditions.

|||

Unfortunately the query and the views are created by a 3rd party application. I won't embarrass the company involved other than to say they are one of the bigger players in mid-range CRM software. The way their application uses the SQL database often leaves a lot to be desired, and I really can't mess with their choice of naming convention.

This application often contains lazy views which refer to 'table.*' rather than pick out the fields that are required. Unfortunately it is difficult for me to know which are needed or not.

I have made changes to the view as you have suggested, but it appears to have made little if any difference. I'm not really surprised however as if a view was slow, wouldn't you expect it it always be slow if run on exactly the same set of data? How can I explain that sometimes it runs in a second, while other times 14 minutes?|||

Hi!

To answer your question about why fast then slow... Caching...

Once the data has been cached it no longer has to go to the data pages to resolve it.

So as long as the data has not changed (or cache expired) it will resolve from cache.

Can you send me the showplan on this... maybe we can solve this with some simple indexing.

chas@.hyman.com

Charles Hyman

Senior Consultant

MCTS SQL Server 2005

MCTS Biztalk Server

MCTS Vista Config

MCITP Database Administration

MCITP Database Developer

TALLAN Inc.

www.tallan.com

|||My initial guess would be from what you describe the table or index is locked. That is where you time difference is coming from.

Look at the locks and see when it takes long, if there is a lock on the table by someone.

|||

Please post the execution plan. Without that, everything else is just a WAG*.

* Wild-Assumptions Guess

Intermittent query slowdowns and corresponding high CPU utilization

Hi. We are experiencing an issue on SQL Server that sounds very simlar to
the hotifx detailed here: http://support.microsoft.com/kb/835864
Only trouble is we are running on a single CPU server when the article
suggests it is only applicable to multple CPU servers. Has anyone had
experience with this issue and resolving it?
--
McGeeky
http://mcgeeky.blogspot.comOn Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>the hotifx detailed here: http://support.microsoft.com/kb/835864
>Only trouble is we are running on a single CPU server when the article
>suggests it is only applicable to multple CPU servers. Has anyone had
>experience with this issue and resolving it?
I may be seeing it myself on a small, old two-processor box running on
only 512mb. Thanks for pointing out the article.
Microsoft is infamous for admitting to one special-case of bugs that
turn up in about a million other environments.
Doesn't guarantee that what you or I see is the same bug, since even
Microsoft may not have verified all the situations.
Josh|||Do you see it happen to any query? We are experiencing it only on one
query - it generally takes only 5-10 seconds but occassionally SQL Server
goes nuts and it takes 20 minutes with CPU on 100%.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:t5n452t4bn1h2r4f7iv4cpaqbutsch2skv@.4ax.com...
> On Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>>the hotifx detailed here: http://support.microsoft.com/kb/835864
>>Only trouble is we are running on a single CPU server when the article
>>suggests it is only applicable to multple CPU servers. Has anyone had
>>experience with this issue and resolving it?
>
> I may be seeing it myself on a small, old two-processor box running on
> only 512mb. Thanks for pointing out the article.
> Microsoft is infamous for admitting to one special-case of bugs that
> turn up in about a million other environments.
> Doesn't guarantee that what you or I see is the same bug, since even
> Microsoft may not have verified all the situations.
> Josh|||On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>Do you see it happen to any query? We are experiencing it only on one
>query - it generally takes only 5-10 seconds but occassionally SQL Server
>goes nuts and it takes 20 minutes with CPU on 100%.
Not at my current location.
Saw something that might have been this at a previous shop, where an
SP in a particular test (load) script goes nuts, but attempts to rerun
with even the same data via query analyzer see no problem.
There were a lot of executions of prepared plans involved, we might
have mismapped the numbers to the source, should have run a test with
SP statement start and plan capture set in profiler, so I can't be
ENTIRELY certain. And even if it was what it seemed, the cause might
not have been the memory allocation issue mentioned in the MS
document, since this wasn't really a high-transaction system.
Josh|||Your scenario sounds exactly like mine: its one query in particular but
attempts to run it in query analyzer with the exact same parameters fail to
reproduce the problem.
I like your idea of setting up a trace on the stored procedure plan. A
possibility is that SQL Server gets mixed up occasionally and creates a
really bad access plan.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh|||Can you capture plans in SQL Server 2000?
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh

Intermittent ORDER BY not functionning

Hi!
I have this query running well, except that when
executing some times, the result change and doesn't start
with the urgence24 column order as requested!!! It's
really strange. Does somenone can help me or tell me where
to find?
Does the SELECT INTO with temporary table can make a
problem?
SELECT noLIFNR,
noLIFNR [LIFNR],
raisonSociale,
CASE urgence24 WHEN 1 THEN 'exclamation.gif'
ELSE 'spacer.gif' END AS urgence24,
indRegionRespVentes + '-' + telRespVentes AS
telRespVentes,
respVentes,
indRegion1 + '-' + tel1 AS tel1,
ville,
nomRegion,
nomMrc
INTO #Temp
FROM vFournisseurs
WHERE actif = 1
and version = 1
ORDER BY urgence24 ASC,
raisonSociale ASC,
1 ASC
select * from #temp
Thanks in advance
DavidDavid Parenteau wrote:
> Hi!
> I have this query running well, except that when
> executing some times, the result change and doesn't start
> with the urgence24 column order as requested!!! It's
> really strange. Does somenone can help me or tell me where
> to find?
> Does the SELECT INTO with temporary table can make a
> problem?
Yes. While ORDER BY will order the results selected, the insertion order
into the temp table is not guaranteed.
<snip>
> ORDER BY urgence24 ASC,
> raisonSociale ASC,
> 1 ASC
> select * from #temp
The only way to guarantee a particular order when selecting rows is to use
an ORDER BY clause in the SELECT statement that retrieves the rows:
select * from #temp
ORDER BY urgence24 ASC,
raisonSociale ASC,
1 ASC
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||IIRC, your technique is not guaranteed. There is a thread with the subject
"order by and identity" that recently discussed various techniques for doing
this, one of which is currently "guaranteed" according to MS.
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0d8401c503ad$94e0e780$a601280a@.phx.gbl...
> Hi!
> I have this query running well, except that when
> executing some times, the result change and doesn't start
> with the urgence24 column order as requested!!! It's
> really strange. Does somenone can help me or tell me where
> to find?
> Does the SELECT INTO with temporary table can make a
> problem?
> SELECT noLIFNR,
> noLIFNR [LIFNR],
> raisonSociale,
> CASE urgence24 WHEN 1 THEN 'exclamation.gif'
> ELSE 'spacer.gif' END AS urgence24,
> indRegionRespVentes + '-' + telRespVentes AS
> telRespVentes,
> respVentes,
> indRegion1 + '-' + tel1 AS tel1,
> ville,
> nomRegion,
> nomMrc
> INTO #Temp
> FROM vFournisseurs
> WHERE actif = 1
> and version = 1
> ORDER BY urgence24 ASC,
> raisonSociale ASC,
> 1 ASC
> select * from #temp
>
> Thanks in advance
> David|||Wow... I have read some part of that thread, but my
example does'nt use a IDENTITY column. How it applies the
same way? Does the problem is the SELECT INTO that don't
insert the rows after the ORDER BY occurs?
I need to give an answer to my client.
Thanks!
David

>--Original Message--
>IIRC, your technique is not guaranteed. There is a
thread with the subject
>"order by and identity" that recently discussed various
techniques for doing
>this, one of which is currently "guaranteed" according to
MS.
>"David Parenteau" <david.parenteau@.compuware.com> wrote
in message
>news:0d8401c503ad$94e0e780$a601280a@.phx.gbl...
start
where
>
>.
>|||> Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
A table should be treated as an unordered set of rows. You need to specify
ORDER BY when selecting *from* the table in order to guarantee a particular
sequence. Insertion sequence isn't relevant in this case.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...
> Wow... I have read some part of that thread, but my
> example does'nt use a IDENTITY column. How it applies the
> same way? Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
> I need to give an answer to my client.
> Thanks!
> David
>
> thread with the subject
> techniques for doing
> MS.
> in message
> start
> where|||My understanding is that the guaranteed technique involves the creation of a
temp table with an identity column and the use of an insert/select
statement. However, the topic of the referenced thread is not exactly the
same as the issue you are experiencing (now that I read the post a bit more
carefully). Bob's post is actually more appropriate.
I don't particularly understand why you use a temp table when you can simply
return the result set generated select/into statement (just omit the "into"
part). In any event, you should always consider a table as an unordered set
of rows and that a select statement without an order by clause is never
guaranteed to return the rows in any consistent order. I believe that is
your fundamental flaw. In addition, I also find fault with the use of the
asterisk ("*"), especially if this is production code. Both of these issues
are frequently discussed in the newsgroup.
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...
> Wow... I have read some part of that thread, but my
> example does'nt use a IDENTITY column. How it applies the
> same way? Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
> I need to give an answer to my client.
> Thanks!
> David
>
> thread with the subject
> techniques for doing
> MS.
> in message
> start
> where|||David,
Your concern is not with the order in which rows
are inserted into #temp. Your concern is with the order
of rows in the result set of the select query:
SELECT * FROM #temp
At least that's what it sounds like - sometimes you see
the resulting rows in an order you don't want.
The easy solution, and the only guaranteed solution, if
you need to see results in a particular order, is to add
an ORDER BY clause. You will always get the order
you want if you execute
SELECT * FROM #temp
ORDER BY urgence24, raisonSociale, noLIFNR
The rows in a table are not in any order - they are
more like apples in a bag. They don't automatically
fall out of the bag in the exact order they went into
the bag.
In this particular case, the order in which the
rows were inserted is irrelevant, since you have no
IDENTITY column to record that order. The issue
here is the order or results from your SELECT query,
and that can be controlled with ORDER BY. Parallelism,
indexes, concurrency, and many other factors can change
the order in which the rows are returned, if there is no
ORDER BY clause.
Steve Kass
Drew University
David Parenteau wrote:
>Wow... I have read some part of that thread, but my
>example does'nt use a IDENTITY column. How it applies the
>same way? Does the problem is the SELECT INTO that don't
>insert the rows after the ORDER BY occurs?
>I need to give an answer to my client.
>Thanks!
>David
>
>
>thread with the subject
>
>techniques for doing
>
>MS.
>
>in message
>
>start
>
>where
>|||Ok, thanks for the *, I practice this too. The code here
was only the first part of all my code.
That table is used later to get only the record in the
middle of the entire result set (a particular page).
I need the order, as adviced here, only after that SELECT
INTO, to get always the same rows in the same page (row
460 to 469 to get the page 46 for example)
Thanks a lot of your help all you guys!

>--Original Message--
>My understanding is that the guaranteed technique
involves the creation of a
>temp table with an identity column and the use of an
insert/select
>statement. However, the topic of the referenced thread
is not exactly the
>same as the issue you are experiencing (now that I read
the post a bit more
>carefully). Bob's post is actually more appropriate.
>I don't particularly understand why you use a temp table
when you can simply
>return the result set generated select/into statement
(just omit the "into"
>part). In any event, you should always consider a table
as an unordered set
>of rows and that a select statement without an order by
clause is never
>guaranteed to return the rows in any consistent order. I
believe that is
>your fundamental flaw. In addition, I also find fault
with the use of the
>asterisk ("*"), especially if this is production code.
Both of these issues
>are frequently discussed in the newsgroup.
>"David Parenteau" <david.parenteau@.compuware.com> wrote
in message
>news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...|||Well that clarifies things. Usually questions about a particular technique
generate "better" answers when the reason driving its usage is included.
Perhaps you will find the following link useful:
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:072801c503b7$34654e00$a401280a@.phx.gbl...
> Ok, thanks for the *, I practice this too. The code here
> was only the first part of all my code.
> That table is used later to get only the record in the
> middle of the entire result set (a particular page).
> I need the order, as adviced here, only after that SELECT
> INTO, to get always the same rows in the same page (row
> 460 to 469 to get the page 46 for example)
> Thanks a lot of your help all you guys!
>
> involves the creation of a
> insert/select
> is not exactly the
> the post a bit more
> when you can simply
> (just omit the "into"
> as an unordered set
> clause is never
> believe that is
> with the use of the
> Both of these issues
> in message
>|||Thanks, I will go to that page :)
David
>--Original Message--
>slipped ... and that link is
http://www.aspfaq.com/show.asp?id=2120
>"Scott Morris" <bogus@.bogus.com> wrote in message
>news:eKW$No7AFHA.1400@.TK2MSFTNGP11.phx.gbl...
particular
>technique
usage is included.
in message
here
SELECT
(row
thread
read
table
table
by
order. I
code.
wrote
>
>.
>

intermittent openquery error "..The OLE DB provider MSDASQL indicates that the object has

Help,

I am running a pass through query to oracle from SQL server 2000 as
follows;

select * from openquery(nbsp, 'select * from FND_FLEX_VALUES')

I have run this query through both DTS and the query analyzer and get
the foloowing error;

Server: Msg 7357, Level 16, State 2, Line 3
Could not process object 'select * from FND_FLEX_VALUES'. The OLE DB
provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to
process object, since the object has no columnsProviderName='MSDASQL',
Query=select * from FND_FLEX_VALUES'].

The really strange thing is, I'll get this error the first time I
execute the query but if I execute it immeadiatley after it will run
fine.

Any help would be most appreciated!

Cheers"Kevin" <kevin.morrell@.nbs.nhs.uk> wrote in message
news:3fff8324.0409150032.783cfd99@.posting.google.c om...
> Help,
> I am running a pass through query to oracle from SQL server 2000 as
> follows;
> select * from openquery(nbsp, 'select * from FND_FLEX_VALUES')
> I have run this query through both DTS and the query analyzer and get
> the foloowing error;
> Server: Msg 7357, Level 16, State 2, Line 3
> Could not process object 'select * from FND_FLEX_VALUES'. The OLE DB
> provider 'MSDASQL' indicates that the object has no columns.
> OLE DB error trace [Non-interface error: OLE DB provider unable to
> process object, since the object has no columnsProviderName='MSDASQL',
> Query=select * from FND_FLEX_VALUES'].
> The really strange thing is, I'll get this error the first time I
> execute the query but if I execute it immeadiatley after it will run
> fine.
> Any help would be most appreciated!
> Cheers

There are a couple of KB articles for error 7357, but they only relate to
situations where the query doesn't return a result set, which doesn't seem
to be the case with your query:

http://support.microsoft.com/defaul...8&Product=sql2k
http://support.microsoft.com/defaul...9&Product=sql2k

Is there some reason why you're using the ODBC OLE DB provider instead of
the Oracle one (MSDAORA)? You might want to try the Oracle one to see if
that makes a difference, and upgrading MDAC to the latest version might also
be worth a try.

Simon

Intermittent Logon

Most of the time all the users can login to SQL.
On occation one or two will fail.
If I then try their login using Query Analyser it works...and then they are
able to log in.
What's going on?!?!?!
Kyle!
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:OJpLo7X0EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Most of the time all the users can login to SQL.
> On occation one or two will fail.
> If I then try their login using Query Analyser it works...and then they
are
> able to log in.
> What's going on?!?!?!
Have you enabled failed login auditing on both the server and SQL Server?
Steve
|||When it fails what error do they see?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Intermittent Logon

Most of the time all the users can login to SQL.
On occation one or two will fail.
If I then try their login using Query Analyser it works...and then they are
able to log in.
What's going on?!?!?!
Kyle!"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:OJpLo7X0EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Most of the time all the users can login to SQL.
> On occation one or two will fail.
> If I then try their login using Query Analyser it works...and then they
are
> able to log in.
> What's going on?!?!?!
Have you enabled failed login auditing on both the server and SQL Server?
Steve|||When it fails what error do they see?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Intermittent Logon

Most of the time all the users can login to SQL.
On occation one or two will fail.
If I then try their login using Query Analyser it works...and then they are
able to log in.
What's going on?!?!?!
Kyle!"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:OJpLo7X0EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Most of the time all the users can login to SQL.
> On occation one or two will fail.
> If I then try their login using Query Analyser it works...and then they
are
> able to log in.
> What's going on?!?!?!
Have you enabled failed login auditing on both the server and SQL Server?
Steve|||When it fails what error do they see?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Intermittent issue with outer joins on subqueries

Hello, we are running into an issue which seems to be a bug in the way
MSSQL joins on subqueries. The below query is run daily by a scheduled
task and usually runs correctly. About once a week the query produces
a resultset that makes it appear that the outer join on the subquery
(alias: POINFO) was changed to an inner join. The resultset has only a
percentage of the records it should have as a result. Are there any
know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
problem happens on a Win2000 server. Thanks.
SELECT *
FROM rc.dbo.LineItems LI
INNER JOIN rc.dbo.orders O
ON O.guidPK = LI.orderguidPK
INNER JOIN decode DECODE
ON O.status = DECODE.status
INNER JOIN products P
ON LI.productPK = P.PK
/* The below join periodically seems to become an inner join
*/
LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
from purchase_orders PO
INNER JOIN purchase_orders_lines POL
ON PO.PK = POL.purchaseorderPK
INNER JOIN fulfillment_products FP
ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
INNER JOIN I18NManufacturer MAN
ON FP.manPK = MAN.PK
INNER JOIN suppliers S
ON FP.supplierPK = SM.PK
WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
ON O.order_number = POINFO.order_number AND LI.productPK =
POINFO.productPK
WHERE O.order_type <> 1 and O.active=1
and O.status between 2 and 32
Can you capture the plan when the bad result happens, and compare it to the
normal plan?
(In other words, does it happen frequently enough that if you tried, you
could reproduce the problem in Query Analyzer?)
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK =
> POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32
|||I would be much more likely to think the WHERE clause is limiting the rows
'unexpectedly'. Perhaps you should set up an audit trail whereby you store
the COUNT(*) of the entire SELECT as well as the COUNT(*) for the SELECT
without the join to POINFO by itself into a table each time this process is
run (with enough identifying information added to ensure you can pinpoint
the rows for each run). This will help you see if you are affecting fewer
rows with the main query simply because of the WHERE clause or is the LOJ
magically translating into an INNER JOIN. That will give you definitive
evidence to take to Microsoft if it is a bug too (which I will definitely
admit isn't out of the question). :-)
Doing a quick check of post-SP4 hotfixes, see if this one applies
http://support.microsoft.com/kb/892310/
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK =
> POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32

Intermittent issue with outer joins on subqueries

Hello, we are running into an issue which seems to be a bug in the way
MSSQL joins on subqueries. The below query is run daily by a scheduled
task and usually runs correctly. About once a week the query produces
a resultset that makes it appear that the outer join on the subquery
(alias: POINFO) was changed to an inner join. The resultset has only a
percentage of the records it should have as a result. Are there any
know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
problem happens on a Win2000 server. Thanks.
SELECT *
FROM rc.dbo.LineItems LI
INNER JOIN rc.dbo.orders O
ON O.guidPK = LI.orderguidPK
INNER JOIN decode DECODE
ON O.status = DECODE.status
INNER JOIN products P
ON LI.productPK = P.PK
/* The below join periodically seems to become an inner join
*/
LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
from purchase_orders PO
INNER JOIN purchase_orders_lines POL
ON PO.PK = POL.purchaseorderPK
INNER JOIN fulfillment_products FP
ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
INNER JOIN I18NManufacturer MAN
ON FP.manPK = MAN.PK
INNER JOIN suppliers S
ON FP.supplierPK = SM.PK
WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
ON O.order_number = POINFO.order_number AND LI.productPK = POINFO.productPK
WHERE O.order_type <> 1 and O.active=1
and O.status between 2 and 32Can you capture the plan when the bad result happens, and compare it to the
normal plan?
(In other words, does it happen frequently enough that if you tried, you
could reproduce the problem in Query Analyzer?)
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK => POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32|||I would be much more likely to think the WHERE clause is limiting the rows
'unexpectedly'. Perhaps you should set up an audit trail whereby you store
the COUNT(*) of the entire SELECT as well as the COUNT(*) for the SELECT
without the join to POINFO by itself into a table each time this process is
run (with enough identifying information added to ensure you can pinpoint
the rows for each run). This will help you see if you are affecting fewer
rows with the main query simply because of the WHERE clause or is the LOJ
magically translating into an INNER JOIN. That will give you definitive
evidence to take to Microsoft if it is a bug too (which I will definitely
admit isn't out of the question). :-)
Doing a quick check of post-SP4 hotfixes, see if this one applies
http://support.microsoft.com/kb/892310/
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK => POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32

Friday, March 23, 2012

Interfacing with SQL

What is the best and quickest way to develop, or download, an interface that
clients can use to query sql?
Thanks,
CJ
"Illicom Newsgroups" <chrisj@.illicom.net> wrote in message
news:hRHzc.3428$Hf.1942321@.newshog.newsread.com...
> What is the best and quickest way to develop, or download, an interface
that
> clients can use to query sql?
>
Would the SQL Server client utilities like Query Analyzer be of value to
you?
Steve
|||Hi
You may want to check out the some of these:
http://www.aspfaq.com/show.asp?id=2442
John
"Illicom Newsgroups" <chrisj@.illicom.net> wrote in message
news:hRHzc.3428$Hf.1942321@.newshog.newsread.com...
> What is the best and quickest way to develop, or download, an interface
that
> clients can use to query sql?
> Thanks,
> CJ
>
|||> What is the best and quickest way to develop, or download, an
> interface that clients can use to query sql?
a web based interface like myLittleAdmin could be a good solution
more info on http://www.myLittleTools.net/mla_sql
best regards
elian chrebor
// myLittleTools.net : leading provider of web-based applications.
// myLittleAdmin : online MS SQL manager
// http://www.mylittletools.net
// webmaster@.mylittletools.net
|||Chris,
One option...
'SQL Server Web Data Administrator'
http://www.microsoft.com/downloads/d...displaylang=en
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Illicom Newsgroups" <chrisj@.illicom.net> wrote in message
news:hRHzc.3428$Hf.1942321@.newshog.newsread.com...
> What is the best and quickest way to develop, or download, an interface
that
> clients can use to query sql?
> Thanks,
> CJ
>

Interface-less SMO?

One of the typical uses of DMO was to instantiate COM objects via t-sql either via stored procedures or ad-hoc submissions through Query Anaylzer/OSQL/ISQL. This allows me to construct helper scripts that have access to objects outside the SQL Server process space, and I don't have to create any application (console or gui) to do what I need.

It seems that SMO is not meant to be 'interface-less' as we could do with DMO, is this true? If this is the case, can we plan on either SMO being able to instantiate objects without an interface, or can we depend on DMO hanging around for a little while longer, while SMO "ramps up"?

Or, should I just start planning on learning how to create my own 'interface-less' objects via CLR, which seems to be the only choice (sofar)?

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Some tutorials on using SMO with PowerShell...

http://www.simple-talk.com/sql/database-administration/managing-sql-server-using-powersmo/

Dan

|||

The main reason I prefer DMO is that it's much easier to encapsulate it into t-sql and have it run from within sql server (job/sp/batchfile). I don't want to have to start writing applications to do what I used to be able to do with DMO.

As an example, I have an sp that gets executed via job; the sp uses DMO in it's body to determine the drive space information on the server and the job emails the resultset as an attachment. Very easy and tidy, one job and one sp.

Now that DMO is being deprecated, I have to get rid of my sp code and create an application, or invoke a powershell script instead of just running an sp and emailing the results as a text file. Can I do this same exact operation with SMO, and not require an application interface?

It just seems a bit odd, that something as core as the way DMO can be used without an interface isn't part of SMO... I can't be the only one out here who does things in this manner.

|||

Jens K. Suessmeyer wrote:
You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

I'm a DBA looking to manage my servers without any more applications than neccessary, not a developer writing software...

|||

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

|||

Euan Garden wrote:

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

It is "tough" to re-write in TSQL what SMO *already* have. We (DBAs with large number of server / databases) used DMO out-of-the box and yes sp_OA* to automate "EASILY" across servers. Now you are asking to deploy PowerShell (another add-on) in order to use SMO from TSQL. Not easy to deploy it all over the place.

|||

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

Neither SMO nor DMO was designed to be called inside the server, there is at least one memory leak in DMO that can not be fixed and there are lots of threading issues. I strongly encourage you not to do it until there is a version desiged to be called inside the server.

|||

Euan Garden wrote:

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

I have to side with Noeld still on this. Most DBA's are aware that there are potential issues with using the sp_OA* procedures internally. However, most of us are not creating large DMO objects internally. Most of us are going after configuration values (like, say, BackupDirectory) which is extremely difficult to get to via t-sql without DMO (it can be done, but it's a LOT more code). Myself, I've been using a custom set of routines that I've written over the years on several hundred servers and only once have I had an issue with DMO causing an error on the server.

Being able to query for configuration values internally means that I only have to deploy my code to the server and it is 'self-contained' at that point. Why not use what we run (SQL Servers) to get the information we need? Why provide the sp_OA* procedures in the first place if they weren't meant to be used (just being rhetorical)?

I'm very glad to see that this topic got a few more replies, this is a topic I think is quite mis-understood.

|||

sp_Oa was provided as a technology solution and it still provides a solution today, thats not to say that I would recomend it. A couple of other examples, SQLMail, in its time was a really cool feature, but on reflection calling MAPI(a non thread safe client focussed API) from inside an Extended Stored Procedure is not going to increase the reliability of your server. SQL Server still supports XPs, I would always look to do something in SQLCLR before an XP however.

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

|||

Euan Garden wrote:

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

That's mostly how I came up wtih the DMO scripts I use today, sniffing EM and whatnot... I'll have to look into sniffing the DMO itself though, that I haven't tried.

Interface-less SMO?

Or, should I just start planning on learning how to create my own 'interface-less' objects via CLR, which seems to be the only choice (sofar)?

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Some tutorials on using SMO with PowerShell...

http://www.simple-talk.com/sql/database-administration/managing-sql-server-using-powersmo/

Dan

|||

It just seems a bit odd, that something as core as the way DMO can be used without an interface isn't part of SMO... I can't be the only one out here who does things in this manner.

|||

Jens K. Suessmeyer wrote:
You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

I'm a DBA looking to manage my servers without any more applications than neccessary, not a developer writing software...

|||

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

|||

Euan Garden wrote:

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

|||

Euan Garden wrote:

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

I'm very glad to see that this topic got a few more replies, this is a topic I think is quite mis-understood.

|||

Euan Garden wrote:

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

That's mostly how I came up wtih the DMO scripts I use today, sniffing EM and whatnot... I'll have to look into sniffing the DMO itself though, that I haven't tried.

Interface-less SMO?

Or, should I just start planning on learning how to create my own 'interface-less' objects via CLR, which seems to be the only choice (sofar)?

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Some tutorials on using SMO with PowerShell...

http://www.simple-talk.com/sql/database-administration/managing-sql-server-using-powersmo/

Dan

|||

It just seems a bit odd, that something as core as the way DMO can be used without an interface isn't part of SMO... I can't be the only one out here who does things in this manner.

|||

Jens K. Suessmeyer wrote:
You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

I'm a DBA looking to manage my servers without any more applications than neccessary, not a developer writing software...

|||

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

|||

Euan Garden wrote:
Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.
Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.
You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.
You are using that COM interface to call a large complex COM library whos primary function is to
Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.
If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

|||

Euan Garden wrote:
Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

I'm very glad to see that this topic got a few more replies, this is a topic I think is quite mis-understood.

|||

Euan Garden wrote:

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

That's mostly how I came up wtih the DMO scripts I use today, sniffing EM and whatnot... I'll have to look into sniffing the DMO itself though, that I haven't tried.

Interface for Visual C++ in VS 2005?

I've been tasked to understand and develop an easy interface to query (insert, select, update, etc) a sql server compact database on an x86 win ce 5 machine. I'm using VS 2005 and have all the necessary SDK's installed. The problem is I can't find any good tutorials or documentation on sql server compact coding in C++ (only in C# and VB). Do y'all have any suggestions on where I can get started to learn the basic c++ sql interface coding?

Thanks!
Jeff

Edit: I'd like to note that I can run the sample northwind project on a win ce 5 x86 emulator, so it's not the setup that I need help with, but the actual coding.

Here are a couple of good places where you can start:

http://www.codeproject.com/ce/#Database

http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.perform(list_folder)=&

|||Thanks for the articles. I like what I see with the ATL OLE DB Consumer Templates articles you wrote, but are they still valid for Visual Studio 2005 or just with eVC++ 3 and 4? I tried converting the sample projects, but I got a ton of compile errors as if the atl library was now very different than the one used in the sample project.

Thanks for any light you can shed!

Jeff
|||

Hi ,

I'am also looking for the same thing.If you get the information, please post it.My requirement is Windows Ce 5 as the mobile device and sql ce 3 and above as the backend.I have tried with the articles posted in the codeproject.com resulting with no luck. I have also tried with ADO and ATL OLE DB but resulting in tons of errors.Please help me in this regard.

|||

I am still using the same headers in VS2005. It's a bit of a kludge, but the Windows Mobile 5 SDK do not ship with the newer versions of the consumer templates (you can see these headers in the Win32 SDK). So far these have worked without issues.

|||Hi,

Thank you very much for quickly reacting to my problem.I have tried to convert a pocket pc 2003 Database application which is shipped with Sql ce 2.0 named Northwindoledb.

I have taken a win32 smart device application and copied all the files from Northwindoledb application and made the following changes .

I have removed the following files from "stdafx.h" header file

"oledb.h"
"oledberr.h"

I have included the following header file to my application.

"ssceoledb.h"

After this I have added the code right after

// Microsoft SQL Server for Windows CE 2.0 Provider (Microsoft.SQLSERVER.OLEDB.CE.2.0)
//
extern const OLEDBDECLSPEC GUID CLSID_SQLSERVERCE_2_0 = {0x76A85B2E,0x9DE0,0x4ded,{0x8E,0x69,0x4D,0xEF,0xDB,0x9C,0x09,0x17}};

in the ssceoledb.h header file.

// Microsoft SQL Server Lite for Windows 3.0 Provider (Microsoft.SQLSERVER.OLEDB.CE.3.0)
//
// {32CE2952-2585-49a6-AEFF-1732076C2945}
//
extern const OLEDBDECLSPEC GUID CLSID_SQLSERVERCE_3_0 = {0x32ce2952, 0x2585, 0x49a6, {0xae, 0xff, 0x17, 0x32, 0x7, 0x6c, 0x29, 0x45}};

and after this add the following line to the "ssceoledb.h" header file

typedef DWORD DBROWSTATUS;

And installed the sqlce 3.0 on windows CE 5.0 mobile.

The application has started running.

But question to you is how can I convert the Same project to MFC Dialog Based application.

Please help me in this regard.

Bye..

J.V.Sivaram

Interesting SQL...

Anyone else experience this?

A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second.

SELECT
dbo.contract_co.producer_num_id, contract_co_status
FROM
dbo.contract_co,
dbo.v_contract_co_status
WHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id )
AND contract_co_status = 'Pending'
OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )

SELECT
dbo.contract_co.producer_num_id, contract_co_status
FROM dbo.contract_co
INNER JOIN dbo.v_contract_co_status
ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_id
WHERE contract_co_status = 'Pending'
OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )The two queries look like they will give different results, too. The first one appears to include a cartesian join. The OR in the where clause makes all the difference.|||Don't want to sound like a snob, but it's all due to the order of processing by QP:

1. JOIN
2. GROUP
3. WHERE
4. HAVING

By rewriting the old query you filtered out what the first query had to deal with while still trying to JOIN.|||actually, i believe it's

1. JOIN
2. WHERE
3. GROUP
4. HAVING

Interesting SQL query requirement for <SELECT> menu

Hi All

Wondered if you could help me with the below query.

I have 1 simple table called STOCKCATS that consists of 2 fields.

These fields are called CATID and LEVEL.

The contents of this table are as follows:

CATID LEVEL
cat01 <nothing>
cat02 <nothing>
cat03 cat01
cat04 <nothing>
cat05 cat01
cat06 cat02
cat07 cat04
etc.. etc...

The way this table works is that I have an ASP page that allows the user to
create a stock category at 2 levels, category level and sub-category level.

When I file the entered data into the table, if the user has chosen to
create a category level stock category then the LEVEL field is left blank
and if they chose to create a sub-category level category then I post the
relevant category level stock category code in the LEVEL field. For
example, in the above list cat01 is a category level stock category and
cat05 is a sub-category as it is a sub-category of cat01.

My query is that I want to populate a simple HTML <SELECT> menu (using ASP),
but instead of it being a straightforward 'select catid from stockcats order
by catid', I want to group this list into some kind of order, eg:

instead of:

cat01 <nothing> << I need to bring back this 2nd column so that I can
do a simple IF THEN in asp to indent sub-cats
cat02 <nothing>
cat03 cat01
cat04 <nothing>
cat05 cat01
cat06 cat02
cat07 cat04

I would like

cat01 <nothing> << ditto
cat03 cat01
cat05 cat01
cat02 <nothing>
cat06 cat02
cat04 <nothing>
cat07 cat04

Do you know if this is possible in pure SQL (I must confess that I'm using
MySQL, but I would have thought the SQL syntax would be the same if it is
possible) or a combo of ASP & SQL?

Thanks

RobbieOn Mon, 7 Nov 2005 14:45:41 -0000, Astra wrote:

>Hi All
>Wondered if you could help me with the below query.
>I have 1 simple table called STOCKCATS that consists of 2 fields.
>These fields are called CATID and LEVEL.
>The contents of this table are as follows:
>CATID LEVEL
>cat01 <nothing>
>cat02 <nothing>
>cat03 cat01
>cat04 <nothing>
>cat05 cat01
>cat06 cat02
>cat07 cat04
>etc.. etc...
>The way this table works is that I have an ASP page that allows the user to
>create a stock category at 2 levels, category level and sub-category level.
>When I file the entered data into the table, if the user has chosen to
>create a category level stock category then the LEVEL field is left blank
>and if they chose to create a sub-category level category then I post the
>relevant category level stock category code in the LEVEL field. For
>example, in the above list cat01 is a category level stock category and
>cat05 is a sub-category as it is a sub-category of cat01.

Hi Robbie,

I'm not too happy with this design. Categories are not the same thing as
sub-categories, so you shouldn't lump them together in the same table.

CREATE TABLE Categories
(CatName varchar(10) NOT NULL,
PRIMARY KEY (CatName)
)
CREATE TABLE SubCategories
(SubCatName varchar(10) NOT NULL,
CatName varchar(10) NOT NULL,
PRIMARY KEY (SubCatName),
FOREIGN KEY (CatName) REFERENCES Categories (CatName)
)

>My query is that I want to populate a simple HTML <SELECT> menu (using ASP),
>but instead of it being a straightforward 'select catid from stockcats order
>by catid', I want to group this list into some kind of order, eg:
>instead of:
>cat01 <nothing> << I need to bring back this 2nd column so that I can
>do a simple IF THEN in asp to indent sub-cats
>cat02 <nothing>
>cat03 cat01
>cat04 <nothing>
>cat05 cat01
>cat06 cat02
>cat07 cat04
>I would like
>cat01 <nothing> << ditto
>cat03 cat01
>cat05 cat01
>cat02 <nothing>
>cat06 cat02
>cat04 <nothing>
>cat07 cat04
>Do you know if this is possible in pure SQL (I must confess that I'm using
>MySQL, but I would have thought the SQL syntax would be the same if it is
>possible) or a combo of ASP & SQL?

If you change the design as I suggest, then it's as simple as

SELECT CatName, NULL AS SubCatName
FROM Categories
UNION ALL
SELECT CatName, SubCatName
FROM SubCategories
ORDER BY CatName, SubCatName

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Astra (No@.Spam.com) writes:
> I would like
> cat01 <nothing> << ditto
> cat03 cat01
> cat05 cat01
> cat02 <nothing>
> cat06 cat02
> cat04 <nothing>
> cat07 cat04
> Do you know if this is possible in pure SQL (I must confess that I'm using
> MySQL, but I would have thought the SQL syntax would be the same if it is
> possible) or a combo of ASP & SQL?

I believe this query would work in SQL Server:

SELECT CATID, LEVEL
FROM STOCKCATS
ORDER BY coalesce(LEVEL, CATID), LEVEL

But I don't think it this conforms to ANSI standards, so it may not run
in MySQL.

If you want help with MySQL, you are probably better off asking in
comp.databases.mysql or some other MySQL forum.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Many thanks guys

Apologies for multi-post.

Rgds Robbie

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9707F156E1626Yazorman@.127.0.0.1...
Astra (No@.Spam.com) writes:
> I would like
> cat01 <nothing> << ditto
> cat03 cat01
> cat05 cat01
> cat02 <nothing>
> cat06 cat02
> cat04 <nothing>
> cat07 cat04
> Do you know if this is possible in pure SQL (I must confess that I'm using
> MySQL, but I would have thought the SQL syntax would be the same if it is
> possible) or a combo of ASP & SQL?

I believe this query would work in SQL Server:

SELECT CATID, LEVEL
FROM STOCKCATS
ORDER BY coalesce(LEVEL, CATID), LEVEL

But I don't think it this conforms to ANSI standards, so it may not run
in MySQL.

If you want help with MySQL, you are probably better off asking in
comp.databases.mysql or some other MySQL forum.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, March 30, 2012

Intermittently Slow query - Left Join

intermittent stalling on a query

Wednesday, March 28, 2012

Intermittent slow SQL2005 database

Intermittent query slowdowns and corresponding high CPU utilization

Intermittent ORDER BY not functionning

intermittent openquery error "..The OLE DB provider MSDASQL indicates that the object has

Intermittent Logon

Intermittent Logon

Intermittent Logon

Intermittent issue with outer joins on subqueries

Intermittent issue with outer joins on subqueries

Friday, March 23, 2012

Interfacing with SQL

Interface-less SMO?

Interface-less SMO?

Interface-less SMO?

Interface for Visual C++ in VS 2005?

Interesting SQL...

Interesting SQL query requirement for <SELECT> menu

Integrated Security

Blog Archive

About Me