Loading ...
Sorry, an error occurred while loading the content.

RE: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index - issue

Expand Messages
  • Andy Jerison
    It all depends on the size of the tables. My database and transaction volume are tiny compared to what you re working with. Andy J ... From:
    Message 1 of 83 , Sep 1, 2011
    • 0 Attachment
      It all depends on the size of the tables. My database and transaction volume
      are tiny compared to what you're working with.

      Andy J

      -----Original Message-----
      From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com] On Behalf
      Of Alan Brookes
      Sent: Wednesday, August 31, 2011 6:06 PM
      To: magicu-l@yahoogroups.com
      Subject: Re: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index - issue

      Well, Mr J...

      We rarely disagree about such matters but...

      Building hash tables is an unnecessary overhead if you have the right
      indexes; certainly in SQL Server 2000, which most
      of my apps use.

      Submitting them to the Tuning Advisor ends up ACTUALLY creating the physical
      indexes anyway; what's the difference?

      I have customers who load 1,000,000 records a day into some tables, and by
      analysing their queries and adding new,
      appropriate indexes, I was able to return result sets approx 10,000 times
      faster (their measurement, I would add) than
      their queries previously did.

      It's "a matter of letting the RDBMS do it's work".

      And I never have PKs. No point other than satisfying Codd's rules.




      Best Regards

      Alan

      On 31-08-2011 22:57, Andy Jerison wrote:
      > Hi Alan,
      >
      > I rarely define real indexes except on primary keys. The SQL Server
      > optimizer is smart enough to select an appropriate index or build a hash
      > table for a query, and on relatively small tables, often no indexes except
      > the PK are necessary. For performance optimization I build sample queries
      in
      > UP, submit them to the SQL Server Database Engine Tuning Advisor, and let
      it
      > build real indexes when necessary. It's a matter of letting the engine do
      > the work....
      >
      > Andy J
      >
      > -----Original Message-----
      > From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com] On Behalf
      > Of Alan Brookes
      > Sent: Wednesday, August 31, 2011 5:39 PM
      > To: magicu-l@yahoogroups.com
      > Subject: Re: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index - issue
      >
      > Hi Andy.
      >
      > A virtual index is just that - virtual; it simply generates an Order By
      > clause that further restricts the optimizer.
      >
      > The optimizer cannot use it to return a result set - it MUST table scan.
      > Jaaasonik is lucky that his table has only 200 rows; there is no
      indication
      > that that may not grow to 10,000,000 rows in
      > the future. Have you seen the result of table scanning 10,000,000 rows?
      > Painful.
      >
      > The storage for the extra (2-segment) index is negligible and performance
      > will be almost instantaneous. It will also
      > allow the RDBMS to create& store statistics to enable even better data
      > retrieval.
      >
      >
      >
      >
      > Best Regards
      >
      > Alan
      >
      > On 31-08-2011 20:05, Andy Jerison wrote:
      >> Hi Alan,
      >>
      >>>> you REALLY, R E A L L Y need a Real index on the table<<
      >>
      >> Why a Real index? I think a Virtual index will work just as well, with
      >> marginally less overhead. Jaaasonik tells us the table has only 200 rows,
      > so
      >> SQL Server will return the result just as fast either way.
      >>
      >> Andy J
      >>
      >> P.S. Jaaasonik, I wonder if you could sign your posts so we can address
      > you
      >> by name. I find it easier to have a friendly conversation with someone I
      > can
      >> name. Thanks!
      >>
      >> -----Original Message-----
      >> From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com] On
      Behalf
      >> Of Alan Brookes
      >> Sent: Wednesday, August 31, 2011 2:53 PM
      >> To: magicu-l@yahoogroups.com
      >> Subject: Re: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index - issue
      >>
      >> OK. I've just reviewed the log output again.
      >>
      >> Firstly, is the task in which you are doing the Link Online or Batch?
      >>
      >> Secondly, are you _certain_ it has no Main Table?
      >>
      >> Thirdly, can you confirm (or otherwise) that - reading by the index isn -
      >> that the first record in the result set has
      >> status 10 and isn 35?
      >>
      >> Fourthly, can you post the export of the offending program to either this
      >> Group's file area or include it in a reply
      >> (ideally, with the uniPaaS table definition) - this Group is an amazingly
      >> erudite resource and it will help a lot with
      >> solving the issue - and I believe it is NOT the gateway...?
      >>
      >> Fifthly, if you need to do this Link, you REALLY, R E A L L Y need a
      Real
      >> index on the table with login1 as the primary
      >> segment (my recommendation is for a Unique Real index comprising
      >> login1+isn).
      >>
      >>
      >>
      >>
      >> Best Regards
      >>
      >> Alan
      >>
      >> On 31-08-2011 18:43, Jaaanosik wrote:
      >>> No date columns in a real index. There is only one real index, isn -
      >> integer identity.
      >>>
      >>> The table has about 200 rows. There is no reason under these
      > circumstances
      >> for uP to query the table endlessly. There must some kind of end
      condition
      >> missed in the gw.
      >>>
      >>>
      >>> <Q>So where is UP getting the status=10 and isn>= 35? The task must have
      >>> either a task level range expression, or a where clause, or some
      >> additional
      >>> locate criteria in a link (either on virtual or real fields).</Q>
      >>>
      >>> One of the rows has status=10 and isn=35.
      >>> If you check one of my first posts you'll see the gw log.
      >>> I think this one:
      >> http://tech.groups.yahoo.com/group/magicu-l/message/162464
      >>>
      >>>
      >>>
      >>>
      >>>
      >>> --- In magicu-l@yahoogroups.com, Alan Brookes<alanjbrookes@...>
      wrote:
      >>>>
      >>>> Do you have any date columns in the table that are part of a Real
      index?
      >>>>
      >>>>
      >>>>
      >>>>
      >>>> Best Regards
      >>>>
      >>>> Alan
      >>>>
      >>>> On 31-08-2011 16:47, Jaaanosik wrote:
      >>>>> Here is the latest development...
      >>>>>
      >>>>> The SQL Server and table are SQL_Latin1_General_CP1_CI_AS.
      >>>>>
      >>>>> SSMS studio query "select logon1,status,isn from myDB.dbo.logon where
      >> logon1 = 'Bob'" returns rows as expected even though all of them have
      > value
      >> 'bob'.
      >>>>>
      >>>>> uP does not find them and starts looking with additional where clause:
      >>>>> SELECT logon1,status,isn FROM myDB.dbo.logon WHERE (logon1 = 'Bob' )
      > AND
      >> ( logon1 = 'Bob' AND status = 10 AND isn>= 35)
      >>>>>
      >>>>> When the virtual key is non-unique then uP stops after x attempts but
      >> never finds a proper result.
      >>>>> When the virtual key is unique then uP ends up in an endless loop.
      >>>>>
      >>>>> When link query goes with 'bob' then everything is fine.
      >>>>> One simple query and done as expected.
      >>>>>
      >>>>> There are three issues:
      >>>>> 1. uP should work properly with logon1='Bob' as in my other working
      >> environment.
      >>>>> 2. uP should never end up in an endless loop.
      >>>>> 3. The error "Requested conversion is not supported." is still there
      >> when I try to execute direct SQL. Working environment is fine though.
      >>>>>
      >>>>> That's about it.
      >>>>>
      >>>>>
      >>>>>
      >>>>>
      >>>>> --- In magicu-l@yahoogroups.com, "sylvain.raymond@"<sraymond@>
      > wrote:
      >>>>>>
      >>>>>> Thank you,
      >>>>>> Sylvain
      >>>>>>
      >>>>>> --- In magicu-l@yahoogroups.com, "Keith Canniff"<kcanniff@>
      wrote:
      >>>>>>>
      >>>>>>> Just as a note: The 32bit MSSQL gateway works fine on both Windows
      >> 2005 and
      >>>>>>> 2008 servers (64bit).
      >>>>>>>
      >>>>>>> I have a client that moved from one to the other and worked in both
      >>>>>>> environments (on over 10 servers)
      >>>>>>>
      >>>>>>>
      >>>>>>>
      >>>>>>> From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com] On
      >> Behalf
      >>>>>>> Of sylvain.raymond@
      >>>>>>> Sent: Wednesday, August 31, 2011 7:17 AM
      >>>>>>> To: magicu-l@yahoogroups.com
      >>>>>>> Subject: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index - issue
      >>>>>>>
      >>>>>>>
      >>>>>>>
      >>>>>>>
      >>>>>>>
      >>>>>>> I am curious about:
      >>>>>>> SQL 2008 R2 (10.0.4064) and uP1.8.
      >>>>>>> My hypothesis is that it will not work, as it does not with uP1.9.
      >>>>>>>
      >>>>>>> To my knowledge, the UP gateway is 32bit.
      >>>>>>> I am no SQL Server guru, but I think that you need to make sure that
      >> the UP
      >>>>>>> Gateway communicates with some SQL 32bit client which could (but I
      am
      >> not
      >>>>>>> sure 100%) work with the SQL server 64bit.
      >>>>>>>
      >>>>>>> Sylvain
      >>>>>>>
      >>>>>>> --- In magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      > ,
      >>>>>>> "Jaaanosik"<jaaanosik@> wrote:
      >>>>>>>>
      >>>>>>>> SQL 10.0.2531 and uP1.8 was OK. The server and SQL are 64bit.
      >>>>>>>>
      >>>>>>>> --- In magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >> ,
      >>>>>>> "sylvain.raymond@"<sraymond@> wrote:
      >>>>>>>>>
      >>>>>>>>> "Win 2008 R2 server, SQL 2008 R2 (10.0.4064)"
      >>>>>>>>>
      >>>>>>>>> The above is fairly new. We are not too many running such
      >> combination
      >>>>>>> here, I suspect.
      >>>>>>>>>
      >>>>>>>>> Can (have) you try UP1.8 on this. My guess is that you may have
      the
      >> same
      >>>>>>> problem with it as with 1.9.
      >>>>>>>>>
      >>>>>>>>> Is the beast above 32 or 64 bits?
      >>>>>>>>> The UP gateway is a 32 bits program. The SQL client would need to
      > be
      >> 32
      >>>>>>> bits in order to work with UP's gateway.
      >>>>>>>>>
      >>>>>>>>> Otherwise, I could only recommend you get MSE to look at it on the
      >> same
      >>>>>>> exact combination of server and SQL server. They might be able to
      >> reproduce
      >>>>>>> it then.
      >>>>>>>>>
      >>>>>>>>> Meanwhile, I would not throw the towel on UP for this, but I would
      >>>>>>> definitely postpone deployment on the above Server/SQL Service
      >> combination
      >>>>>>> until you can make your programs work on it.
      >>>>>>>>>
      >>>>>>>>> Good luck,
      >>>>>>>>> Sylvain
      >>>>>>>>>
      >>>>>>>>> --- In magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >> ,
      >>>>>>> "Jaaanosik"<jaaanosik@> wrote:
      >>>>>>>>>>
      >>>>>>>>>> The issue is on a Win 2008 R2 server, SQL 2008 R2 (10.0.4064).
      >>>>>>>>>> My desktop Win7 SQL 2008 R2 (10.50.1617) is fine.
      >>>>>>>>>> It's uP1.9g on both machines.
      >>>>>>>>>>
      >>>>>>>>>> What table scan you are talking about?
      >>>>>>>>>>
      >>>>>>>>>>
      >>>>>>>>>> --- In
      magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >> ,
      >>>>>>> "Andy Jerison"<ajerison@> wrote:
      >>>>>>>>>>>
      >>>>>>>>>>> Hi ???,
      >>>>>>>>>>>
      >>>>>>>>>>> Maybe you mentioned this earlier in this thread but it got
      >>>>>>> truncated. In any
      >>>>>>>>>>> case, I don't remember reading that this problem was specific to
      >>>>>>> certain
      >>>>>>>>>>> computers.
      >>>>>>>>>>>
      >>>>>>>>>>> If that's correct, I'd look closely at the shortcuts your users
      >> are
      >>>>>>> using to
      >>>>>>>>>>> start UP. They must be pointing to different INI files.
      >>>>>>>>>>>
      >>>>>>>>>>> Here's another theory: maybe the difference is different users
      >>>>>>> rather than
      >>>>>>>>>>> different computers, or the SQL Server is using Windows
      >>>>>>> authentication and
      >>>>>>>>>>> the computers are logging in automatically to the same account
      >> each
      >>>>>>> time.
      >>>>>>>>>>> The problem computers are logged in as users whose SQL security
      >>>>>>> prevents the
      >>>>>>>>>>> database from returning a row. That fact combined with the way
      >> Link
      >>>>>>> Query
      >>>>>>>>>>> works in certain situations makes the database perform a table
      >> scan.
      >>>>>>>>>>>
      >>>>>>>>>>> Andy J
      >>>>>>>>>>>
      >>>>>>>>>>> -----Original Message-----
      >>>>>>>>>>> From:
      magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >>>>>>> [mailto:magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >> ] On
      >>>>>>> Behalf
      >>>>>>>>>>> Of Jaaanosik
      >>>>>>>>>>> Sent: Monday, August 29, 2011 2:16 PM
      >>>>>>>>>>> To: magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >>>>>>>>>>> Subject: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index -
      >> issue
      >>>>>>>>>>>
      >>>>>>>>>>> Unfortunately the customer log will not tell you much.
      >>>>>>>>>>> That's when dev log comes handy.
      >>>>>>>>>>> I went through the logs and this is the first major discrepancy
      >>>>>>> between
      >>>>>>>>>>> working and not working computers.
      >>>>>>>>>>>
      >>>>>>>>>>> In one case it closes the cursor:
      >>>>>>>>>>> ,13638 ms7_crsr_fetch():<<<<< ctxID = -1.000000, retcode =
      >> 0
      >>>>>>>>>>> ,13638 ms7_crsr_close():>>>>> ctxID = -1.000000, crsr_hdl =
      0
      >>>>>>>>>>>
      >>>>>>>>>>> In the other case it continues to fetch more data and never
      finds
      >>>>>>> the
      >>>>>>>>>>> result:
      >>>>>>>>>>> ,98609 ms7_crsr_fetch():<<<<< ctxID = -1.000000, retcode =
      >> 0
      >>>>>>>>>>> ,98609 ms7_crsr_fetch():>>>>> ctxID = -1.000000, dbd_hdl =
      0,
      >> lock =
      >>>>>>> FALSE
      >>>>>>>>>>>
      >>>>>>>>>>>
      >>>>>>>>>>> --- In
      > magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >>>>>>> , "Andy Jerison"<ajerison@> wrote:
      >>>>>>>>>>>>
      >>>>>>>>>>>> Hi,
      >>>>>>>>>>>>
      >>>>>>>>>>>> My first suggestion is a way to make your debugging life
      easier:
      >>>>>>> set "Log
      >>>>>>>>>>> Level" to "Customer", not "Developer". Confusingly, "Customer"
      in
      >>>>>>> the
      >>>>>>>>>>> Logging/DBMS dialog means *you* -- a customer of MSE.
      "Developer"
      >>>>>>> means
      >>>>>>>>>>> someone at MSE who debugs or develops the gateway.
      >>>>>>>>>>>>
      >>>>>>>>>>>> As to whether you can trust Unipaas, yes, you can. But it pays
      > to
      >>>>>>>>>>> understand what happens behind the scenes, so your current
      > efforts
      >>>>>>> will
      >>>>>>>>>>> eventually bear fruit. :-)
      >>>>>>>>>>>>
      >>>>>>>>>>>> Andy J
      >>>>>>>>>>>>
      >>>>>>>>>>>> -----Original Message-----
      >>>>>>>>>>>> From:
      > magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >>>>>>> [mailto:magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >> ] On
      >>>>>>> Behalf
      >>>>>>>>>>> Of Jaaanosik
      >>>>>>>>>>>> Sent: Monday, August 29, 2011 12:46 PM
      >>>>>>>>>>>> To: magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >>>>>>>>>>>> Subject: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index -
      >>>>>>> issue
      >>>>>>>>>>>>
      >>>>>>>>>>>> Andy,
      >>>>>>>>>>>> My vKey has two segments logon1 and status.
      >>>>>>>>>>>> If gw adds isn (which is primary key - identity column) then
      > it's
      >>>>>>> fine.
      >>>>>>>>>>>> What I don't understand is why it's adding status = 10 and isn
      >>>>>>>> =35.
      >>>>>>>>>>>> Never asked for that.
      >>>>>>>>>>>>
      >>>>>>>>>>>> I have similar example on computer where it works fine.
      >>>>>>>>>>>> One sql statement, clean, simple.
      >>>>>>>>>>>> Here is dev gw log at the end of the task:
      >>>>>>>>>>>>
      >>>>>>>>>>>> ,13638 ms7_esqlc_close(): ICommand:Release() retcode = 0
      >>>>>>>>>>>> ,13638 ms7_esqlc_close():<<<<< retcode = 0
      >>>>>>>>>>>> ,13638 ms7_crsr_fetch(): pCursor->strtpos = 0
      >>>>>>>>>>>> ,13638 ms7_crsr_fetch():<<<<< ctxID = -1.000000, retcode =
      >> 0
      >>>>>>>>>>>> ,13638 ms7_crsr_close():>>>>> ctxID = -1.000000, crsr_hdl =
      0
      >>>>>>>>>>>> ,13638 ms7_crsr_close(): ctxID = -1.000000, pConnection =
      >> 053AF778
      >>>>>>>>>>>> ,13638 ms7_cursor_alloc():>>>>> cursor name - Range
      >>>>>>>>>>>> ,13638 ms7_cursor_alloc(): cursor exist, doing nothing
      >>>>>>>>>>>> ,13638 ms7_cursor_alloc():<<<<< cursor index = 0
      >>>>>>>>>>>> ,13638 ms7_crsr_close():<<<<< ctxID = -1.000000, retcode =
      >>>>>>> RET_OK
      >>>>>>>>>>>> ,13638 ms7_trans():>>>>> ctxID = -1.000000, transmode = 8,
      db
      > =
      >>>>>>> 20
      >>>>>>>>>>>> ,13638 ms7_trans(): ctxID = -1.000000, pConnection = 053AF778
      >>>>>>>>>>>> ,13638 session - 0, in use - 1, write - 1, reusable - 1,
      results
      >>>>>>> pending
      >>>>>>>>>>> - 0,not only for cursors - 1
      >>>>>>>>>>>> ,13638 ms7_trans(): COMMIT
      >>>>>>>>>>>> ,13638 ms7_esqlc_commit()>>>>>
      >>>>>>>>>>>> ,13638 SET IMPLICIT_TRANSACTIONS OFF
      >>>>>>>>>>>> ,13638 ms7_esqlc_commit():<<<<< errcode = 0
      >>>>>>>>>>>> ,13638 ms7_perform_release():>>>>> ctxID = -1.000000,
      > serverID
      >> =
      >>>>>>> 1,
      >>>>>>>>>>> conn_hdl = 0
      >>>>>>>>>>>> ,13638 ms7_perform_release():<<<<< ctxID = -1.000000,
      > serverID
      >> =
      >>>>>>> 1,
      >>>>>>>>>>> conn_hdl = 0
      >>>>>>>>>>>> ,13638 ms7_trans():<<<<< ctxID = -1.000000, retcode =
      0
      >>>>>>>>>>>> Commit Transaction 0
      >>>>>>>>>>>> Starts Record Suffix
      >>>>>>>>>>>> Flow - Update: 'E: a'<== exp #2
      >>>>>>>>>>>> Ends Record Suffix
      >>>>>>>>>>>> Starts Closing Batch Task - 'check' (Task Instance: 64)
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>> Here is dev gw log on computer where it uP goes nuts:
      >>>>>>>>>>>> ,98609 ms7_esqlc_close(): ICommand:Release() retcode = 0
      >>>>>>>>>>>> ,98609 ms7_esqlc_close():<<<<< retcode = 0
      >>>>>>>>>>>> ,98609 ms7_crsr_fetch(): pCursor->strtpos = 0
      >>>>>>>>>>>> ,98609 ms7_crsr_fetch():<<<<< ctxID = -1.000000, retcode =
      >> 0
      >>>>>>>>>>>> ,98609 ms7_crsr_fetch():>>>>> ctxID = -1.000000, dbd_hdl =
      0,
      >>>>>>> lock =
      >>>>>>>>>>> FALSE
      >>>>>>>>>>>> ,98609 ms7_crsr_fetch(): ctxID = -1.000000, pConnection =
      >> 06A7E170
      >>>>>>>>>>>> ,98609 ms7_cursor_alloc():>>>>> cursor name - Range
      >>>>>>>>>>>> ,98609 ms7_cursor_alloc(): cursor exist, doing nothing
      >>>>>>>>>>>> ,98609 ms7_cursor_alloc():<<<<< cursor index = 0
      >>>>>>>>>>>> ,98609 ms7_crsr_fetch(): database = myDB, table name = logon,
      >>>>>>> crsr_hdl =
      >>>>>>>>>>> 0
      >>>>>>>>>>>> ,98609 ms7_startpos_open():>>>>> crsr->strtpos_cnt = 0,
      >> last_pos
      >>>>>>> = 1
      >>>>>>>>>>>> ,98609 ms7_cursor_alloc():>>>>> cursor name - Range
      >>>>>>>>>>>> ,98609 ms7_cursor_alloc(): cursor exist, doing nothing
      >>>>>>>>>>>> ,98625 ms7_cursor_alloc():<<<<< cursor index = 0
      >>>>>>>>>>>> ,98625 ms7_get_key_for_startpos():>>>>> database = myDB,
      > table
      >>>>>>> name =
      >>>>>>>>>>> logon, crsr_hdl=0, last_pos = 1
      >>>>>>>>>>>> ,98625 ms7_cursor_alloc():>>>>> cursor name - Gkey
      >>>>>>>>>>>> ,98625 ms7_cursor_alloc(): cursor does not exist, allocating
      new
      >>>>>>>>>>>> ,98625 ms7_cursor_alloc(): cursor rows = 0
      >>>>>>>>>>>> ,98625 ms7_cursor_alloc():<<<<< cursor index = 1
      >>>>>>>>>>>> ,98625 SqldaFree():>>>>> nothing to free
      >>>>>>>>>>>> ,98625 SqldaAlloc():>>>>> number of elements = 3, sqlvar
      size
      > =
      >>>>>>> 308
      >>>>>>>>>>>> ,98625 SqldaAlloc():<<<<< name - SQLDA225
      >>>>>>>>>>>> ,98625 ms7_get_key_for_startpos(): view = TRUE, poskey =
      >> 87246436,
      >>>>>>>>>>> sortkey = 87246472
      >>>>>>>>>>>> ,98625 SqldaGetKey():>>>>>
      >>>>>>>>>>>> ,98625 SqldaGetKey(): fld_idx = 3, name = logon1
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>> uP starts to issue lots of statements after this.
      >>>>>>>>>>>> If vKey is set to be unique then it's even endless loop.
      >>>>>>>>>>>> I have to kill it.
      >>>>>>>>>>>> I have a big application. If this happens I am in deep deep
      ....
      >>>>>>>>>>>>
      >>>>>>>>>>>> The big question for me is can I trust uP?
      >>>>>>>>>>>> I won't till I find the answer. If I don't find the explanation
      >>>>>>> then good
      >>>>>>>>>>> by uP.
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>>
      >>>>>>>>>>>> --- In magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com> , "Andy Jerison"<ajerison@>
      >> wrote:
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> A little more to ponder: the gateway adds segments to the
      ORDER
      >>>>>>> BY
      >>>>>>>>>>> clause until it has enough to return a single row. This will
      vary
      >>>>>>> depending
      >>>>>>>>>>> on the data source's definition. In my example, it added the
      >> primary
      >>>>>>> key's
      >>>>>>>>>>> column name.
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> I still have no clue where status and isn are coming from in
      >>>>>>> your ORDER
      >>>>>>>>>>> BY though.
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> Andy J
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> -----Original Message-----
      >>>>>>>>>>>>> From: magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com>
      >> [mailto:magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com> ] On
      >>>>>>>>>>> Behalf Of Andy Jerison
      >>>>>>>>>>>>> Sent: Monday, August 29, 2011 12:18 PM
      >>>>>>>>>>>>> To:
      magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >>>>>>>
      >>>>>>>>>>>>> Subject: RE: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual
      index
      >>>>>>> - issue
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> Drat, no ... The virtual index is on Metropolitan_Area.
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> AJ
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> -----Original Message-----
      >>>>>>>>>>>>> From: magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com>
      >> [mailto:magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com> ] On
      >>>>>>>>>>> Behalf Of Andy Jerison
      >>>>>>>>>>>>> Sent: Monday, August 29, 2011 12:15 PM
      >>>>>>>>>>>>> To:
      magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >>>>>>>
      >>>>>>>>>>>>> Subject: RE: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual
      index
      >>>>>>> - issue
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> I'd guess the status and isn columns appear in the query
      > because
      >>>>>>> it's
      >>>>>>>>>>> fetching more rows. Do you have a range on one of the linked
      >>>>>>> columns? If so,
      >>>>>>>>>>> the engine will have to return multiple rows in order to find
      > rows
      >>>>>>> that
      >>>>>>>>>>> satisfy the range.
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> Beyond that, I'm not sure where to go with this. I've just
      >>>>>>> tested a link
      >>>>>>>>>>> query on a virtual index and it returned sensible SQL:
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> SELECT Metropolitan_Area,Manufacturing_Ctr_ID
      >>>>>>>>>>>>> FROM TANK2020.dbo.Mfg_Centers
      >>>>>>>>>>>>> WHERE Metropolitan_Area = 'Dayton, OH'
      >>>>>>>>>>>>> ORDER BY Metropolitan_Area ASC ,Manufacturing_Ctr_ID ASC
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> The virtual index is on Manufacturing_Ctr_ID.
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> Andy J
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> -----Original Message-----
      >>>>>>>>>>>>> From: magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com>
      >> [mailto:magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com> ] On
      >>>>>>>>>>> Behalf Of Jaaanosik
      >>>>>>>>>>>>> Sent: Monday, August 29, 2011 11:23 AM
      >>>>>>>>>>>>> To:
      magicu-l@yahoogroups.com<mailto:magicu-l%40yahoogroups.com>
      >>>>>>>
      >>>>>>>>>>>>> Subject: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index -
      >>>>>>> issue
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> Thanks Chris,
      >>>>>>>>>>>>> It's a good idea but too much work to fix the issue. We have a
      >>>>>>> big
      >>>>>>>>>>> application.
      >>>>>>>>>>>>> I'd rather find out what's going on.
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> The sql statement does not make sense to me:
      >>>>>>>>>>>>> SELECT logon1,status,isn FROM myDB.dbo.logon WHERE
      >>>>>>>>>>>>> (logon1 = 'Bob' ) AND ( logon1 = 'Bob' AND status = 10 AND isn
      >>>>>>> >= 35)
      >>>>>>>>>>> ORDER BY logon1 ASC ,status ASC
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> My link operation is asking to locate 'Bob'. Where are the
      >>>>>>> status and
      >>>>>>>>>>> isn conditions coming from?
      >>>>>>>>>>>>> This is what scares me, ... a thought that I can not trust the
      >>>>>>> gateway.
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> I know it's a special case that I have but still.
      >>>>>>>>>>>>> Now it's an issue of trust towards uniPaaS.
      >>>>>>>>>>>>>
      >>>>>>>>>>>>>
      >>>>>>>>>>>>>
      >>>>>>>>>>>>>
      >>>>>>>>>>>>> --- In magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com> , "Chris de Bijl"<c.debijl@>
      >> wrote:
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>> When working with SQL Server and using link query, uniPaaS
      >>>>>>> retrieves a
      >>>>>>>>>>> result set of data and always scan the complete result set. And
      >> not
      >>>>>>> only the
      >>>>>>>>>>> first row like when working with Pervasive.
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>> So try to avoid to use a link query when you expect more then
      >>>>>>> one row
      >>>>>>>>>>> in the result set. This will downgrade the performance of your
      >>>>>>> programs.
      >>>>>>>>>>>>>> These problems you will also see when searching for the last
      >>>>>>> or first
      >>>>>>>>>>> record in a table or when one of the locate parameters (from or
      >> to)
      >>>>>>> is not
      >>>>>>>>>>> filled in.
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>> To solve this problem, make a view:
      >>>>>>>>>>>>>> Create view V_Logon as SELECT distinct logon1,status FROM
      >>>>>>>>>>> myDB.dbo.logon
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>> Use this view for your link query.
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>> Best regards,
      >>>>>>>>>>>>>> Chris
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>> From: Jaaanosik
      >>>>>>>>>>>>>> Sent: Saturday, August 27, 2011 3:09 PM
      >>>>>>>>>>>>>> To: magicu-l@yahoogroups.com
      >>>>>>> <mailto:magicu-l%40yahoogroups.com>
      >>>>>>>>>>>>>> Subject: [magicu-l] Re: uP1.9 MSSQL LinkQuery virtual index -
      >>>>>>> issue
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>> Chris,
      >>>>>>>>>>>>>> The index is set as non unique.
      >>>>>>>>>>>>>> Before it was unique and uP ended up in an infinity loop with
      >>>>>>> the same
      >>>>>>>>>>> select statements.
      >>>>>>>>>>>>>> This is a major problem for us. We have many prgs with simple
      >>>>>>> links to
      >>>>>>>>>>> validate data.
      >>>>>>>>>>>>>>
      >>>>>>>>>>>>>> --- In mailto:magicu-l%40yahoogroups.com, "Chris de Bijl"
      >>>>>>> <c.debijl@>
      >>>>>>>>>>> wrote:
      >>>>>>>>>>>>>>>
      >>>>>>>>>>>>>>> Maybe you forgot to set the virtual index to â€Å"non
      >>>>>>> unique
      >>>>>>>>>>> index”.
      >>>>>>>>>>>>>>>
      >>>>>>>>>>>>>>> You can also use direct sql to solve this problem
      >>>>>>>>>>>>>>> SELECT top 1 logon1,status FROM myDB.dbo.logon WHERE logon1
      >>>>>>> = 'Bob'
      >>>>>>>>>>> ORDER BY logon1 ASC ,status ASC
      >>>>>>>>>>>>>>> or
      >>>>>>>>>>>>>>> SELECT distinct logon1,status FROM myDB.dbo.logon WHERE
      >>>>>>> logon1 =
      >>>>>>>>>>> 'Bob' ORDER BY logon1 ASC ,status ASC
    • Jaaanosik
      Hello, as promised and better late than never. Here is sql and uP table. Insert 2 lines: 1 bob 1 2 bob 1 Create a uP1.9 prg with link query on virtual
      Message 83 of 83 , Sep 8, 2011
      • 0 Attachment
        Hello,
        as promised and better late than never.
        Here is sql and uP table.

        Insert 2 lines:
        1 bob 1
        2 bob 1

        Create a uP1.9 prg with link query on virtual index locating 'Bob'.
        uP ends up in an endless loop.

        CREATE TABLE [dbo].[logon1](
        [isn] [int] IDENTITY(1,1) NOT NULL,
        [logon1] [varchar](100) NOT NULL,
        [status] [smallint] NOT NULL
        ) ON [PRIMARY]

        GO

        CREATE UNIQUE CLUSTERED INDEX [isn] ON [dbo].[logon1]
        (
        [isn] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        GO


        <?xml version="1.0" encoding="UTF-8" standalone="no" ?>
        <Application>

        <Header>
        <Version val="11010.00"/>
        <APPLICATION val="N"/>
        <WithIsn val="N"/>
        <WITH_MODELS val="N"/>
        <MAIN_PRG_VARS val="4"/>
        <MAIN_PRG_DSPS val="1"/>
        <STP END="23" START="23"/>
        </Header>

        <DataSourceRepository>
        <DataObjects>
        <DataObject PhysicalName="logon1" Resident="N" data_source="ProManDB" name="logon1">
        <Encrypt val="N"/>
        <Cache val="D"/>
        <IdentifyRowModifications val="U"/>
        <HasHint val="N"/>
        <Cursor val="D"/>
        <CheckExistence val="D"/>
        <ObjectType val="T"/>
        <Owner val="dbo"/>
        <Position val="D"/>
        <PositionIndex val="1"/>
        <Columns>
        <Column id="1" name="isn">
        <PropertyList model="FIELD">
        <Model attr_obj="FIELD_NUMERIC" id="1"/>
        <Picture id="157" valUnicode="9"/>
        <StoredAs id="173" val="4"/>
        <Size id="174" val="4"/>
        <Definition id="175" val="2"/>
        <DbColumnName id="178" val="isn"/>
        <Type id="179" val="integer identity"/>
        <_Dec id="181"/>
        <_Whole id="182" val="9"/>
        <_Negative id="192"/>
        <_FieldPhysical Name="isn" PIC_U="9" Size="4" SqlType="integer identity" allowed_null="N" attribute="N" context_cookies="Y" database_definition="2" id="231" storage="4" translate="1"/>
        <PartOfDatetime id="244"/>
        <_FieldStyle id="276" val="1"/>
        </PropertyList>
        </Column>
        <Column id="4" name="logon1">
        <PropertyList model="FIELD">
        <Model attr_obj="FIELD_ALPHA" id="1"/>
        <Picture id="157" valUnicode="100"/>
        <NullDefault id="169"/>
        <StoredAs id="173" val="3"/>
        <Size id="174" val="100"/>
        <Definition id="175" val="2"/>
        <DbColumnName id="178" val="logon1"/>
        <Type id="179" val="VARCHAR(100)"/>
        <_Flip id="194"/>
        <_FieldPhysical Name="logon1" PIC_U="100" Size="100" SqlType="VARCHAR(100)" allowed_null="N" attribute="A" context_cookies="Y" database_definition="2" id="231" storage="3" translate="1"/>
        <PartOfDatetime id="244"/>
        </PropertyList>
        </Column>
        <Column id="10" name="status">
        <PropertyList model="FIELD">
        <Model attr_obj="FIELD_NUMERIC" id="1"/>
        <Picture id="157" valUnicode="4"/>
        <StoredAs id="173" val="4"/>
        <Size id="174" val="2"/>
        <Definition id="175" val="2"/>
        <DbColumnName id="178" val="status"/>
        <_Dec id="181"/>
        <_Whole id="182" val="4"/>
        <_Negative id="192"/>
        <_FieldPhysical Name="status" PIC_U="4" Size="2" allowed_null="N" attribute="N" context_cookies="Y" database_definition="2" id="231" storage="4" translate="1"/>
        <PartOfDatetime id="244"/>
        <_FieldStyle id="276" val="1"/>
        </PropertyList>
        </Column>
        </Columns>
        <Indexes>
        <Index id="1" name="PK_logon">
        <Mode val="S"/>
        <Order val="T"/>
        <Range val="Q"/>
        <IndexType val="R"/>
        <PhysicalName val="PK_logon"/>
        <HasHint val="N"/>
        <Clustered val="Y"/>
        <DropOnReindex val="N"/>
        <Primary val="Y"/>
        <Segments>
        <Segment>
        <Size val="4"/>
        <Column val="1"/>
        <Order val="A"/>
        </Segment>
        </Segments>
        </Index>
        <Index id="2" name="vLogon">
        <Mode val="S"/>
        <Order val="T"/>
        <Range val="Q"/>
        <IndexType val="V"/>
        <PhysicalName val="vLogon"/>
        <HasHint val="Y"/>
        <Clustered val="N"/>
        <DropOnReindex val="N"/>
        <Primary val="N"/>
        <Segments>
        <Segment>
        <Size val="100"/>
        <Column val="2"/>
        <Order val="A"/>
        </Segment>
        <Segment>
        <Size val="2"/>
        <Column val="3"/>
        <Order val="A"/>
        </Segment>
        </Segments>
        </Index>
        </Indexes>
        <ForeignKeys/>
        </DataObject>
        </DataObjects>
        </DataSourceRepository>

        </Application>












        --- In magicu-l@yahoogroups.com, "Jaaanosik" <jaaanosik@...> wrote:
        >
        > Thanks Eyal,
        > ... again, the latest development.
        >
        > When I said I have a working environment I made a mistake.
        > My apologies, there was a row with 'Bob' hidden in those 200 rows.
        > It happens, I missed it.
        >
        > In short, when I changed it to 'bob' and LQ looks for 'Bob' uP goes to endless loop even on my W7 machine.
        >
        > I'll play more with rows, columns and I'll try to prepare as small example as possible.
        >
        >
        > --- In magicu-l@yahoogroups.com, "eyal_pfeifel" <eyalp@> wrote:
        > >
        > > Fact 1: Internally, uniPaaS works "case-sensitive" by default.
        > >
        > > Fact 2: When performing a range operation, uniPaaS filters the result set returned from the database according to the range expression.
        > >
        > > So, when working with a database which is "case-insensitive", after the database returns a result set, uniPaaS will filter the result set again, this time using the default "case-sensitive" filter, resulting no rows found.
        > >
        > > In this specific case, when querying for "Bob", "bob" is also returned from the database, but uniPaaS filters this value out because ("Bob" <> "bob") by default.
        > >
        > > The solution: to use an ACS file which is "case-insensitive". This instructs uniPaaS to compare strings in a "case-insensitive" way, instead of the default. This can be done at the database level or for the whole application.
        > >
        > > Eyal.
        > >
      Your message has been successfully submitted and would be delivered to recipients shortly.