473,960 Members | 19,191 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Optimizing help

Hi,

Just wondering if anyone could help me optimise this query, it can take
up to 3 seconds to execute which makes me think theres probably a
better way to do what i'm doing..

SELECT MAX(Latitude) as "MaxLat",MIN(La titude) as
"MinLat",MAX(Lo ngitude) as "MaxLong",Min(L ongitude) as "MinLong",
count(filtered. serialno) as "count"
FROM (
SELECT tl.*
FROM trackinglogs tl LEFT JOIN trackinglogs AS t2
ON tl.nodeid=t2.no deid AND tl.datetimestam p < t2.datetimestam p
WHERE t2.nodeid IS NULL) as filtered
JOIN usernodes un on (filtered.nodei d = un.nodeid)
WHERE un.userid = 99;

Basically i'm trying to get the maximum / minimum latitude and
longitude values from a position log for a particular user , (who may
have > 1 node giving lat long data)...

I've indexed every field used in the where/join statements.

Any advice appreciated, thanks

Nov 23 '05 #1
1 1189
Can you post:

1) the EXPLAIn of that query
2) SHOW INDEX for the tables
3) DESCRIBE of the tables

Thank you!

Nov 25 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
2298
by: JustSomeGuy | last post by:
I have a routine that evaluates a polynomial equation that have 3 variables x,y,z of orders 1,2,3 the coefficients of the polynomial are in an array. This routine is quite slow and I'd like to optimize it. Any suggestions? simply put pts is a vector of typdef struct double x; double y; double z;
5
3558
by: ArShAm | last post by:
Hi there Please help me to optimize this code for speed I added /O2 to compiler settings I added /Oe to compiler settings for accepting register type request , but it seems that is not allowed and if I remove register type for "l" , time of generating codes doesn't change the original code makes some files , but I removed that section to make it simple for you to read please help me to optimize it for faster running
14
1712
by: Ian Richardson | last post by:
I'm writing a large Javascript application (uncompressed source around 400K) which is doing almost all the initialisation it needs to in a just-in-time manner. However, I have included an option for almost all of this to be done when the application first starts. Under this circumstance, and since the last few nightly builds of Mozilla, I've been getting: Script warning:
35
8391
by: Thomas Matthews | last post by:
Hi, My son is writing a program to move a character. He is using the numbers on the keypad to indicate the direction of movement: 7 8 9 4 5 6 1 2 3 Each number has a direction except for '5'. So in his switch statement, he omits a case for '5':
2
1573
by: Brian | last post by:
In particular, this question goes out to the Microsoft C++ development team. Back in June, Ronald Laeremans posted the following message. Has the optimizing compiler been included with the standard edition of VC++ yet? Don't think that we've forgotten about it.... Thank, Brian From: Ronald Laeremans (ronaldl@online.microsoft.com)
4
1485
by: Flashman | last post by:
A little confusing with setting up optimizing options with 2003 .NET. Under the Optimization Tab. if you set to /O1 or /O2 is the program ignoring the settings for Inline Function expansion, Enabling of Intrinsic? Would seem these should be greyed out to let you know. Bigger question is what have people found in setting up the optimizing? Using /O1 or /O2 ? Any ideas would be greatly appreciated.
3
1287
by: Diffident | last post by:
Hello All, I need some help on optimizing a piece of code. Currently this is how we retrieve our Oracle's connection string for executing all the queries in our application. public string GetOracleClientConnectionString() { string strConnectionString = System.Configuration.ConfigurationSettings.AppSettings;
4
3509
by: Got2Go | last post by:
Hello Group, I have a table that has millions of records in it. About 100 records are added every 5 minutes (one per OIDID) (the sample provided below has data for 2 OIDIDs (99 and 100) And I have a webpage that executes 9 queries one after the other, and then displays the results on the webpage. When the database was empty, this process was very quick. But, as the DB grew, it became slower.
24
3219
by: Richard G. Riley | last post by:
Without resorting to asm chunks I'm working on a few small routines which manipulate bitmasks. I'm looking for any guidance on writing C in a manner which tilts the compilers hand in, if possible, a compiler/underlying processor independant way : althought to be fair I cant see this stuff on anything other than x86, but who knows. I found some ok info here: http://www.eventhelix.com/RealtimeMantra/Basics/OptimizingCAndCPPCode.htm...
12
3118
by: Mark E. Fenner | last post by:
Hello all, I have a code where my inner loop looks like: allNew = for params in cases: newObj = copy(initialObject) newObj.modify(params) allNew.append(newObj) return allNew
0
10118
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11759
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
11499
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10839
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
10037
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7562
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6345
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4687
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3703
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.