|
Home > Archive > FoxPro Help and Support > May 2005 > Sql server Views!
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Barley Man 2005-05-24, 8:25 pm |
| I have (now) got copies of the SQL database and their tables. I have created
a project, created remote views to the sql tables.
One of the views I have created is called View6. It shows the list of
fieldnames etc., etc.. But...............
If I issue the instruction in the Command Window "use View6 in 0" it takes
FOREVER to open and it opens in a strange way. The Record Counter in the
bottom line increments the Total number of records over a VERY long period
but it's the SECOND part of the display that increments, not the FIRST part.
I.e., the counter starts by reading 1/300 and then changes to 1/400, 1/500,
1/600.........1/10000, 1/100100, 1/10200 ....... and finally on to 1/3655938!
I can browse the start of the 'view' almost instantly and I can use GOTO and
GO TOP at any stage. However, issuing "Go Bottom" forces the counter to climb
MUCH faster but it STILL takes AGES!
What on EARTH is going on! I only asked it to open the 'table'. It looks
more as though it's IMPORTING the records, not just giving me a view of the
ODBC linked SQL records!
| |
| Mark McCasland 2005-05-25, 3:24 am |
| You should put a filter in the WHERE clause that defines your view so only
the records you need will be retrieved. What you are seeing is how remote
views work. They are just a glorified SQL command that retrieve whatever
records match your WHERE clause. If you have no WHERE clause, then all
records get dragged across the wire. You can also open your view with no
records --
USE MyViewName NODATA
"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:E721A864-7584-424F-BC99- 2D40E3990E69@microso
ft.com...
> I have (now) got copies of the SQL database and their tables. I have
created
> a project, created remote views to the sql tables.
>
> One of the views I have created is called View6. It shows the list of
> fieldnames etc., etc.. But...............
>
> If I issue the instruction in the Command Window "use View6 in 0" it takes
> FOREVER to open and it opens in a strange way. The Record Counter in the
> bottom line increments the Total number of records over a VERY long period
> but it's the SECOND part of the display that increments, not the FIRST
part.
>
> I.e., the counter starts by reading 1/300 and then changes to 1/400,
1/500,
> 1/600.........1/10000, 1/100100, 1/10200 ....... and finally on to
1/ 3655938!
> I can browse the start of the 'view' almost instantly and I can use GOTO
and
> GO TOP at any stage. However, issuing "Go Bottom" forces the counter to
climb
> MUCH faster but it STILL takes AGES!
>
> What on EARTH is going on! I only asked it to open the 'table'. It looks
> more as though it's IMPORTING the records, not just giving me a view of
the
> ODBC linked SQL records!
| |
| Barley Man 2005-05-25, 7:24 am |
| But the response of the system is SO SLOW! The 3,000,000 record file probably
took 25 minutes just to 'open' and that is with the SQL file being on the
same machine as the foxpro code.
If I use NODATA, then I can do nothing with it. I can't browse to get a view
of the data on which to base the next SQL query.
I cannot believe that anyone could consider using Fox with SQL if THAT is
the sort of performance being experienced. i still think I must be doing
something stupid. Any further suggestions?
Ian
"Mark McCasland" wrote:
> You should put a filter in the WHERE clause that defines your view so only
> the records you need will be retrieved. What you are seeing is how remote
> views work. They are just a glorified SQL command that retrieve whatever
> records match your WHERE clause. If you have no WHERE clause, then all
> records get dragged across the wire. You can also open your view with no
> records --
>
> USE MyViewName NODATA
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
> news:E721A864-7584-424F-BC99- 2D40E3990E69@microso
ft.com...
> created
> part.
> 1/500,
> 1/3655938!
> and
> climb
> the
>
>
>
| |
| Frédéric Steczycki [AtoutFox][MVP] 2005-05-25, 7:24 am |
| As it happens to me...i'm asking <s>
ODBC trace log isn't on ?
Fred
Barley Man typed:[color=darkred
]
> But the response of the system is SO SLOW! The 3,000,000 record file
> probably took 25 minutes just to 'open' and that is with the SQL file
> being on the same machine as the foxpro code.
>
> If I use NODATA, then I can do nothing with it. I can't browse to get
> a view of the data on which to base the next SQL query.
>
> I cannot believe that anyone could consider using Fox with SQL if
> THAT is the sort of performance being experienced. i still think I
> must be doing something stupid. Any further suggestions?
>
> Ian
>
> "Mark McCasland" wrote:
>
| |
| Barley Man 2005-05-25, 7:24 am |
| Fred....
where do I find 'odbc trace log' to see if it's switched on? Even a Windows
Search produces no results on that one.
Ian
"Frédéric Steczycki [AtoutFox][MVP]" wrote:
> As it happens to me...i'm asking <s>
>
> ODBC trace log isn't on ?
>
>
> Fred
>
> Barley Man typed:
>
>
>
| |
| Fred Taylor 2005-05-25, 11:24 am |
| Control Panel, Administrative Tools, Data Sources (ODB). Under the Tracing
tab make sure the checkbox isn't checked and/or you're not currently
tracing. Press the Stop button if either one is currently tracing.
Even if it's off, you still don't want to pull all 3,000,000 records over
the wire. Use a condition on your WHERE clause to pull only a subset of
records, something like SELECT * FROM customers WHERE custname="A".
--
Fred
Microsoft Visual FoxPro MVP
"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:81DBDF0B-6C03-42C6-8B73- 055402687C2A@microso
ft.com...[color=darkred]
> Fred....
>
> where do I find 'odbc trace log' to see if it's switched on? Even a
> Windows
> Search produces no results on that one.
>
> Ian
>
>
> "Frédéric Steczycki [AtoutFox][MVP]" wrote:
>
|
|
|
|
|